WKB structure does not match expected size

Question

I'm running an Alteryx workflow where I am pulling data from one database to another PostgreSQL database containg PostGIS. However, when I run the workflow and write the data to the database, I am getting an error saying:

Output Data (18) DataWrap2ODBC::SendBatch: ERROR: WKB structure does not match expected size!;¶Error while executing the query Insert into "table_name"("column_1","column_2","column_3","geom") Values (?,?,?,ST_GeomFromWKB(?,4326))

or in other words, the error is saying that WKB structure does not match expected size. There is a geometry or Spatial Object column in the database that I am trying to send to PostgreSQL..

I thought it might be the Spatial Object Field Size (it's too small) but when I increased it, it didn't do anything. Does anybody know why I am getting this error and how to fix it?

EDIT: This is how the geom table looks like that I am trying to insert into my PostgreSQL PostGIS table:

https://jsfiddle.net/ovr1798k/1/

Answer 1

We are able to use the provided string. The original_string provided is at https://jsfiddle.net/ovr1798k/1/

We are able to process it using the original string wrapped between SELECT ST_AsText(ST_GeomFromWKB(E'\\x and )) We made it human readable using ST_AsText to check the result.

A full working demo to insert your data can be

CREATE TABLE IF NOT EXISTS table_name(
  column_1 text,
  column_2 text,
  column_3 text,
  geom geometry(Polygon,4326)
);

-- We supposed your srid is 4326 but change it if necessary
-- Change <yourlongwkbstring> with your own wkb string
INSERT INTO table_name(column_1, column_2, column_3, geom)
VALUES ('content1', 'content2', 'content3',
ST_SetSrid(ST_GeomFromEWKB(decode('<yourlongwkbstring>', 'hex')), 4326);

You can see a full working code at https://gist.github.com/ThomasG77/dae8e8a76db4fc720fb18d261da4076f (because too long to fit in the answer here). You will need to adapt our solution as we did not use placeholders ?

Answer 2

Finally got it to work thanks to someone's help. What I did when first calling the data is SELECT column_1, column_2, column_3, ST_AsEWKB(geom) FROM table_name and then for the Post-Create SQL statement in the workflow, I did the following SQL statement:

ALTER TABLE table_name ALTER COLUMN geom TYPE geometry(geometry,4326) USING (ST_GeomFromEWKB(geom));