@tomvanderputte I could not reproduce the error so it could be specific to the data in your bytea field. I've attached a test workspace (2019.2). This might be something you need to pursue further with your FME reseller, or include a sample of the problem data here. byteatest.fmw
@tomvanderputte I could not reproduce the error so it could be specific to the data in your bytea field. I've attached a test workspace (2019.2). This might be something you need to pursue further with your FME reseller, or include a sample of the problem data here. byteatest.fmw
HI @markatsafe, just added an example workspace and sql script to create and populate the source table.
@tomvanderputte I could not reproduce the error so it could be specific to the data in your bytea field. I've attached a test workspace (2019.2). This might be something you need to pursue further with your FME reseller, or include a sample of the problem data here. byteatest.fmw
Hi @markatsafe, I made some progress: the INSERT only works when setting Bulk Insert = YES. setting it to NO will yield the same error. I saw in the documentation that bulk insert mode uses the COPY command. So somehow the data is correctly transfered and inserted using that way. Does that help in identifying a solution somehow?
OK I managed to do this, with a work around.
So the problem is that I have bytearray created in Python, that is respected when doing an insert with the SQL COPY command (bulk insert = yes), but not when doing a regular SQL INSERT.
The solution is to encode it first, and in the insert statement, tell Postgres to decode it when writing the data. So we cannot use a Postgres writer, but need an SQLExecutor:
1) although it's already binary data, we need to encode the "mesh" attribute with a BinaryEncoder (HEX).
2a ) then use an SQLExecutor to insert it with this command:
insert into public.mesh_table (ogc_fid, mesh) values
(nextval('mesh_table_ogc_fid_seq'), decode('@Value(mesh)','HEX'))
OR
2b) UPSERT the data with this command:
INSERT INTO
public.mesh_table
(ogc_fid
, mesh)
VALUES (
@Value(ogc_fid)
, decode('@Value(mesh)','HEX') )
ON CONFLICT
(ogc_fid)
DO
UPDATE SET mesh = EXCLUDED.mesh
It's a hassle, but it works.
OK I managed to do this, with a work around.
So the problem is that I have bytearray created in Python, that is respected when doing an insert with the SQL COPY command (bulk insert = yes), but not when doing a regular SQL INSERT.
The solution is to encode it first, and in the insert statement, tell Postgres to decode it when writing the data. So we cannot use a Postgres writer, but need an SQLExecutor:
1) although it's already binary data, we need to encode the "mesh" attribute with a BinaryEncoder (HEX).
2a ) then use an SQLExecutor to insert it with this command:
insert into public.mesh_table (ogc_fid, mesh) values
(nextval('mesh_table_ogc_fid_seq'), decode('@Value(mesh)','HEX'))
OR
2b) UPSERT the data with this command:
INSERT INTO
public.mesh_table
(ogc_fid
, mesh)
VALUES (
@Value(ogc_fid)
, decode('@Value(mesh)','HEX') )
ON CONFLICT
(ogc_fid)
DO
UPDATE SET mesh = EXCLUDED.mesh
It's a hassle, but it works.
@tomvanderputte Thanks for including the reproduction package. We were able to reproduce the error. We'll try and get this addressed.
