Skip to main content
  • EN

Problem:

I have a workbench that reads data from a geopackage and writes this to a postgis table. The workbench simply reads data and writes the data with only one attribute. The primary key, called 'fid', should be generated by postgis and not FME so I do not provide a primary key in FME.

This postgis table has a column called 'fid' which is the primary and has a not null constraint. It creates the primary by auto incrementing using 'nextval('mytable_fid_seq'::regclass)'.

 

- The workbench used to work, and has stopped working suddenly

- other similar workbenches still work on other databases

An (unwanted) solution

The only solution that works is to add an SQL Executor before writing the data. However this slows down the progress a lot. The statement is: 'select nextval('treetops_py_fid_seq') as fid'. I'd rather have a different solution.

Adding a counter that generates numbers and write these as 'fid' works, until it finds a situation where the generated number already exists in the table.

Tried so far

I have tried giving a 'fid' attribute in the user attributes with manual settings. This did not work.

Restarting FME or creating a new workbench does not work.

error message

Bulk copy failed on table 'public.mytable' using delimiter ':'. Error was 'ERROR: null value in column "fid" violates not-null constraint

 

DETAIL: Failing row contains (null, 01010000204071000000000000098D044100000080724C1D41, txt...'

 

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

 

... Last line repeated 2 times ...

 

Bulk copy failed on table 'public.mytable' using delimiter ':'. Error was 'no COPY in progress

 

'

 

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

 

... Last line repeated 2 times ...

Try manually removing the 'fid' column in the writer.

Alternatively look at the tip from @arnebrucksch here:

https://knowledge.safe.com/questions/1703/serial-data-types-and-insert-into-postgresql.html

 

Try manually removing the 'fid' column in the writer.

Alternatively look at the tip from @arnebrucksch here:

https://knowledge.safe.com/questions/1703/serial-data-types-and-insert-into-postgresql.html

 

Thank you for your suggestion. I tried both keeping fid in the writer and I tried removing it, in both situations the method fails.

 

However I tried @arnebrucksch and there are some things to note. bigserial is not a datatype, just a shortcut to creating a sequence. However I manually created a sequence as he suggests and assigned the primary key to this sequence. Now FME no longer throws an error.

My solution which seems to have worked is to remove the old fid and recreate it by hand without using bigserial as a datatype when creating the column with the primary key. In FME I mannually added the column 'name' fid in the 'User Attributes' of the FME postgis writer using serial as 'Type'.

I used the following sql statements.

CREATE SEQUENCE mytable_fid_seq;

ALTER TABLE mytable
ADD COLUMN fid bigint DEFAULT nextval('mytable_fid_seq');

ALTER SEQUENCE mytable_fid_seq
OWNED BY mytable.fid;

ALTER TABLE mytable ADD PRIMARY KEY (fid);

 

However I need to use this on an existing table as well where I'd rather not newly assign the fid values but keep the old ones. Any suggestions?

Reply


Community Stats

30,108
Posts
114,295
Replies
37,743
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.