Serial data types and INSERT into PostgreSQL

If you have and old postgres serial design like: id bigint NOT NULL DEFAULT nextval('serial'::regclass)

Change it to: id bigserial NOT NULL

the default sequence name for serial are: <tablename>_<columnname>_seq

Then you can use "serial" Type in FME, and let the DB do the rest.

A decent workaround is to add a trigger to the serial, I've always struggled with FME's postgres writer, it often tries to write id null when you've removed that attribute from the writer and the workflow:

While this thread is already "well aged" it seems to me, that the reason for this behaviour of FME in cooperation with Postgres-Database has not yet been published.

After experiencing the same strange behaviour (Featurewriter throws error, that an "id" of type "Serial" does not accept NULL value, but the same FeatureWriter has worked before ), if found the obvious reason:

- a table "newtable" has been created with the PostGIS-FeatureWriter

- within "newtable" we defined an id-column "system_id" of type "Serial" (and some additional columns)

- we inserted some datasets.

So far everything is fine.

- now we edit the Name of the table in Postgres into "new_table"

- that results in an adjustment of the column definition for "system_id" into

- the table "new_table" is linked to the sequence with the original table Name!

While postgres is able to handle this sitution, the FME-FeatureWriter is not.

Here is a solution (worked for me):

- write the datasets of the "corrupted" table into a backup-table

- delete the table and all corresponding sequences:

DROP TABLE "new_table";
DROP SEQUENCE IF EXISTS"new_table_system_id_seq","newtable_system_id_seq"CASCADE;

- Create the table again:

CREATE TABLE "new_table"(  system_id serial NOT NULL,...

- Insert the datasets from the backup table into "new_table"

I'm pleased to let you know that in FME 2022 betas build 22220 or newer, we have added support for allowing default values to be written to PostGreSQL/PostGIS.

There is a new writer parameter under Advanced > Missing Attribute Behaviour:

If set to Insert null, the writer inserts NULL values for column values if attributes are missing or NULL on incoming features. 

If set to Insert column default value, the writer inserts the default value for a column when the incoming feature has a missing or NULL attribute. 

Note: This parameter is only enabled when “Bulk Insert” is set to No.