PostGIS writer, excluded fields with defaults still populate

Hopefully the sql will help:

--inside fme with bulk insert off it does:

insert into present_nonclipped.play (objectid, name, changedetext_date, changedetect_uuid)

values(1,'oliver')

---- results in a fail

--I would like fme to actually do:

insert into present_nonclipped.play (objectid, name)

values(1,'oliver')

only two fields are set in the writer, yet with bulk load on or off, it still populates the fields with defaults as NULL.

Not sure if I understand correctly, but I had issues in FME 2017 where it would write values to columns even when these where not defined in the writer. (Inserting them in an existing table where these columns existed.) I had to place a AttributeKeeper before the writer to prevent these attributes get through. Sounds like the same issue?

Be aware, when using an AttributeKeeper to stop FME technical features (like fme_db_operation) it only unexposes them, not remove them. Took me a while to find this was bugging me when writing the same features to a database (update) and Excel...

Yes, I tried to remove the unneeded fields and still no luck. It is still adding a null to all the fields where no data is present rather than just not including the column in the insert statement.

See the error below in the log where all the fields are being defined.

019-04-10 20:10:20| 0.6| 0.0|ERROR |Error executing SQL command ('INSERT INTO "present_nonclipped"."play" ("name", "region", "product_code", "region", "web_url", "et_created", "et_edited", "globalid", "changedetect_date", "changedetect_uuid", "geom") values (E'Ceduna Sub-basin\\, ', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '0102000020E610000002000000B89BA8F19779604010CD8E4A6D9E40C0E406149B733F604058BD2C1B8DED41C0')'): 'ERROR: null value in column "changedetect_date" violates not-null constraint

DETAIL: Failing row contains (1957, Ceduna Sub-basin, Bight Basin

, null, null, null, null, null, null, null, 0102000020...'

2019-04-10 20:10:20| 0.6| 0.0|ERROR |A fatal error has occurred. Check the logfile above for details

Hi @olivermorris,

It seems to me you are inserting a NULL value into an attribute where a constraint (not-null) is present:

"ERROR: null value in column "changedetect_date" violates not-null constraint "

I understand that is not exactly your issue, but that is what is causing the translation to fail.

Correct me if I am wrong but if you do an insert then all the table columns will have a new row, did you try to update existing values? with the DatabaseUpdater transformer you can specify exactly which columns/rows should be updated.

Hope this helps,

Itay

Thanks Itay, the fields that FME is adding NULL to have a default set on them which populates automatically - as long as the field is not mentioned in the insert statement. However for some reason FME does include the fields and passes NULL into that field. This is not desirable because I have functions in postgres that are defaults for a couple of fields that arent easy to replicate in FME and populate through FME.

Basically FME is doing this:

insert into present_nonclipped.play (objectid, name, changedetext_date, changedetect_uuid)

values(1,'oliver')

---- results in a fail

--I would like fme to actually do:

insert into present_nonclipped.play (objectid, name)

values(1,'oliver')

Because these are the only fields I mention or refer to.

Thank you for the help, I will look at other workarounds.

Oliver

Hi @olivermorris,

Check out my answer for a similar question on this forum thread.

In short, this is related to FMEENGINE-8945 and a potential workaround is to use an SQLExecutor to write the features.

You can use a Tester to find features that do not have a value for the column that has a default value then write features that are missing values using a basic INSERT Statement inside the SQLExecutor. By inserting values from an SQL statement, you are allowing the default value to be set by the database - this is the equivalent to executing the SQL statement directly in PGAdmin. You can then use a PostGIS writer to write the remaining features to the table.

Hope this helps.

Many thanks @chrisatsafe - can't help but feel this is going to be quite slow to append content so may not be ideal for millions of rows but should keep me out of trouble until then! Is this something that is going to get attention in the future?

Cheers

Oliver

Hi @olivermorris,

Unfortunately, I don't have an eta for when this will become available but I will be sure to update this post when it does. If you would also like to be notified via email, please Submit a Case referencing FMEENGINE-8945 so your contact information can be linked to the enhancement request.

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.

This option doesn’t seem to work for Postgres identity columns with the GENERATED BY DEFAULT AS IDENTITY set. It still wants to put null in there when the missing attribute behaviour is set to default value.

Strangely it works for the feature writer transformer but not the PostGIS writer which suggests it may be a bug.

Using FME Form 2023.2.