span8
span4
span8
span4
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 ...
我的解决方案,这似乎已经奏效是删除旧的FID和手工创建它,且不会与主键列在使用BIGSERIAL的数据类型。在FME我码的手动添加列“名称”使用串行作为“类型”的FME PostGIS的作家的“用户属性” FID。
我用下面的SQL语句。
CREATE SEQUENCE mytable_fid_seq;ALTER TABLE MYTABLE ADD COLUMN FID BIGINT DEFAULT NEXTVAL( 'mytable_fid_seq');归mytable.fid ALTER SEQUENCE mytable_fid_seq;ALTER TABLE MYTABLE ADD PRIMARY KEY(FID);
不过,我需要使用现有表,以及在那里我宁愿不新分配FID值,但保留旧的。有什么建议么?
Try manually removing the 'fid' column in the writer.
Alternatively look at the tip from@arnebruckschhere:
https://knowledge.safe.com/questions/1703/serial-data-types-and-insert-into-postgresql.html
谢谢你的建议。我尝试都保持在作家FID和我试着删除它,在这两种情况下,该方法失败。
不过我试过@arnebrucksch而有一些事情要注意。BIGSERIAL不是数据类型,只是一个快捷方式创建序列。不过,我手动创建一个序列,他建议和分配的主键到此序列。现在FME不再抛出错误。
© 2020 Safe Software Inc |Legal