I am using FME 2011.
Any suggestions?
I am tyring to insert into a PostgreSQL table where i have a primary key defined as serial (auto increment). I want the database to increment this field and not have to supply the value from FME. FME keeps failing due to the Null constraint on the field. I have tried suppling 'DEFAULT' and 'NEXTVALUE('Seq_name'") as a string to attempt to fake it however both complain that an integer is expected and not text.
Page 1 / 1
Hi,
try to remove (delete) the serial field from the writer. That way FME won't send a null field value PostgreSQL.
David
Thanks for the response David. This was one of the things i tried and i still received the error.
Hi,
Did you try to set the transaction interval to a number larger than the number of features written?
Thanks for the suggestion Itay,
At this point i am merely trying to insert 1 feature so the default transaction interval should be ok.
Hi,
A workaround could be to allocate a primary key value using the SQLExecutor (i.e. select nextvalue('my_pri_key_seq').
However, what you describe worked for me using the following setup:
Thanks peter. This is actaully what i implemented as a workaround. Slows done the process but works nonetheless.
I tried again the approach showed in your example and it did fail. I would suspect maybe a version issue since i am still stuck in fme 2011.
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.
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:
capture.jpg
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
"integer NOT NULL DEFAULT nextval('newtable_system_id_seq'::regclass)"
- 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.
