Skip to main content
Solved

UPDATE in postgis with binary data fails, INSERT works

  • January 31, 2020
  • 5 replies
  • 279 views
T
Forum|alt.badge.img

I have a workspace that is trying to upload a bytearray into an existing PostGIS table, into a ByteA column. The data is generated in a python caller.

Using INSERT, this works fine. There is no problem, and the resulting data can be read from a different client as expected.

However, using UPDATE it fails, stating:

Error executing SQL command ('update "public"."mesh_test" set "ogc_fid" = 694,"mesh" = E'\003\000\000\000\212\032\005H\004\362\336H33C@g\032\005H&\362\336H\205\353\021@\354\031\005H\004\362\336H\205\353\021@\001\000\000\000\000\000\000\000\002\000\000\000\001\000\000\000' WHERE "ogc_fid" = E'694''): 'ERROR:  invalid byte sequence for encoding "UTF8": 0x00

I can circumvent the error by encoding it into base64, but that yields data I can't interpret from another client.

It seems that the update statement is done using a different method than the insert statement, which is odd. Has anyone seen this before?

EDIT: Added example workspace, plus sqlfile to create and populate table. You need to fill in the correct credentials as parameter.

EDIT2: The example workspace creates a new table, but I just tested: insert into the original table works as well perfectly.

EDIT3: INSERTING with bulk update set to NO gives the same error as updating.

Best answer by tomvanderputte

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.

View original
1 Attachments
Did this help you find an answer to your question?

5 replies

M
Forum|alt.badge.img+2
  • markatsafe
  • January 31, 2020

@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

T
Forum|alt.badge.img
markatsafe wrote:

@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.

T
Forum|alt.badge.img
markatsafe wrote:

@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?

T
Forum|alt.badge.img
  • tomvanderputte
  • Author
  • Best Answer
  • February 6, 2020

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.

M
Forum|alt.badge.img+2
  • markatsafe
  • February 6, 2020
tomvanderputte wrote:

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.

Reply


Helpful Members This Week

Community Stats

30,985
Posts
117,433
Replies
38,764
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.