Hi Dean,
When using db sequences with PostgreSQL you set your auto-enter sequence setting to 'db identity', like Christian already explained this assumes a certain naming convention of the sequence object in PostgreSQL. When you create your tables with a DBA tool you can easily let these sequences automatically be created and linked to the table at the same time by using the following datatypes:
- you want the PK column to be an integer with a sequence then set the datatype to 'Serial'
- you want to PK column to be a bigint with a sequence then set the datatype to 'Bigserial'
After creating the tables in this manner you will see that you have the correct datatype (integer or bigint) with a sequence with the proper name linked to this table. it's also added to the default value of the column. So these Serial and Bigserial datatypes are little tricks to create Integer/Bigint columns with a dedicated sequence attached.
Dropping the table will also automatically drop the sequence (like I said, they are linked).
If you don't create the columns this way or you want to add sequences to existing columns you need to create the sequence yourself using the CREATE SEQUENCE 'table_column_seq' syntax.
After you created them you need to add them to the column like so:
- Code: Select all
ALTER TABLE tableName ALTER 'columnName' SET DEFAULT nextval('sequenceName')
This last one is not really needed for working with Servoy because Servoy will call this function before it inserts the data. But since you want to import data via other tools then you need to add this so your PK is filled automatically.
As John said you can also use the DB Sequence option where you have to tell Servoy explicitly what the sequence name is, which means you can then use any naming convention you want. But Servoy defaults to db identity when you use PostgreSQL with sequences I believe.
Like Christian said when you create parent and child records you need to save the parent record first to get the PK value to be used as the foreign key on the child records.
If I remember correctly Servoy is suppose to use some temporary/placeholder id value when using db managed sequences. This should allow for creating related records without saving first but I had issues with that so I always save the parent record first. Perhaps that this works as advertised now, I dunno.
Perhaps someone from Servoy can comment on this?