I have been playing around with servoy on a postgres back end, and have made some behavior observations which might be useful
data integrity
A foreign key constraint in a postgres defined table will throw a java script error in servoy when a user attempts to delete a record that will create orphan daughter records.
There is no comparable constraint available in a servoy relation, only the option to automatically delete all daughter records. This maintains data integrity, but does not protect from accidental deletions. Protection from deletion can probably be realized in other ways.
Adding a servoy relation to an existing foreign key constraint to automatically delete daughter records still throws a java script error. Apparently servoy deletes the mother first, then the daughters, otherwise this should work.
a unique constraint will also throw a java error, I have tried to catch this in a method, but haven’t succeeded yet. There seems to be no comparable integrity control offered in servoy, though an SQL query could be built to check the input before inserting new data into the table.
I assume other constraints to data would behave similarly.
Postgres data types
DATE - will be read by servoy as a timestamp type, but seems to work fine. should help the problem of time involved when comparing two dates.
TIME - behaves similarly to date, thought the field must be formatted properly (ie to show time only) in the form.
REAL - is recognized by servoy as a number type, handles fine.
NUMERIC (P,S) - is recognized by servoy as a number type, handles fine.
SERIAL - this causes problems with servoy, as it also wants to control the serial entry.
You can get around this by defining a table constraint making a column the primary key. Servoy should pick this up fine, but I have had to manually get the servoy sequence started in certain situations. If you prefer to create your tables in PSQL this is useful.
CHAR(N) - is recognized by servoy as a text type, handles fine.
Postgres tables
You can create, drop, and alter tables in PSQL and they will be correctly interpreted by servoy if you quit and restart servoy. The existing forms and methods for certain tables persist in servoy after you trop a table, and you can then re create it will be picked up again by servoy - make sure to check all changes manually in servoy.
If you alter a table name in PSQL, the associated servoy forms will no longer relate to your altered table.[/i]