Solved

PostGIS loading - default values being overwritten by NULL's in feature writer

  • 3 August 2021
  • 8 replies
  • 29 views
oliver.morris
Rank
Badge +10

Hi, I have a postgresql database I am writing to it has the following fields:

    changedetect_date timestamp without time zone NOT NULL DEFAULT now(),
    changedetect_uuid character varying(36) COLLATE pg_catalog."default" NOT NULL DEFAULT uuid_generate_v4(),
    pk_index integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),

 All these fields do not need to be inserted into by FME but FME is trying to insert NULL's into them even through the fields are not explicitly set in my feature writer (set to automatic and does not have these fields).

 

When it inserts postgres gives an error 

'ERROR: null value in column "pk_index" violates not-null constraint DETAIL: Failing row contains (...'

POSTGIS writer: A fatal error has occurred. Check the logfile above for details

Successfully closed POSTGIS database writer

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

What can I do to get around this? I tried using manual instead of automatic fields but FME still tries to populate the pk_index field with NULL.

 

Many Thanks for your help as always!

icon

Best answer by andreaatsafe 4 August 2021, 02:22

View original

8 replies

virtualcitymatt
Rank
Userlevel 4
Badge +26

Hmmm, it could be related to the transaction size or Bulk Mode, by default FME will use bulk mode to insert because it's much faster, however, I think Bulk Mode could be causing the issues here.

 

Try turning off Bulk Mode in the Writer settings to see it it helps.. If that doesn't help you might need to also change the transaction size to 1 (although I don't think you'd need to)

oliver.morris
Rank
Badge +10

Hmmm, it could be related to the transaction size or Bulk Mode, by default FME will use bulk mode to insert because it's much faster, however, I think Bulk Mode could be causing the issues here.

 

Try turning off Bulk Mode in the Writer settings to see it it helps.. If that doesn't help you might need to also change the transaction size to 1 (although I don't think you'd need to)

Thank you for the reply, disabling the bulk mode didn't help. I ended up generating content for these fields so that it would load but this is far from ideal. I dont know why FME is writing NULL to these fields if they arent be declared.

A
Rank
Badge +10

Hi @oliver.morris​ ,

Looks like you've encountered this once again, see a previous post here that might have some ideas for you to work around it.

In short, this is related to a known issue (internally tracked as FMEENGINE-8945). Unfortunately, I do not have an ETA on when the development team plans to address this issue. I will re-raise this with the team.

-Andrea

oliver.morris
Rank
Badge +10

Hi @oliver.morris​ ,

Looks like you've encountered this once again, see a previous post here that might have some ideas for you to work around it.

In short, this is related to a known issue (internally tracked as FMEENGINE-8945). Unfortunately, I do not have an ETA on when the development team plans to address this issue. I will re-raise this with the team.

-Andrea

thanks @andreaatsafe​ I am just surprised no one else has raised this issue, does no one use auto incrementing id fields or uuid's in postgres :)

nielsgerrits
Rank
Userlevel 6
Badge +32

thanks @andreaatsafe​ I am just surprised no one else has raised this issue, does no one use auto incrementing id fields or uuid's in postgres :)

Not sure if I understand correct, but for primay key you can just define this in the writer? This works for me:

2021-08-04_11h03_18

oliver.morris
Rank
Badge +10

thanks @andreaatsafe​ I am just surprised no one else has raised this issue, does no one use auto incrementing id fields or uuid's in postgres :)

thanks @nielsgerrits​ if it was a serial data type it would be ok but alas I am using the defaults:

 

DEFAULT uuid_generate_v4()

and

pk_index integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 )

 

A
Rank
Badge +10

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.

PostGreSQL_Default_value

oliver.morris
Rank
Badge +10

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.

PostGreSQL_Default_value

that is great news, thank you @andreaatsafe​ 

Reply


Community Stats

28,628
Posts
109,152
Replies
36,506
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.