Insert / Update rows in Postgres with column type geometry

App Building
1

Hi,

I'm building an app that connects to a Postgres database with the postgis extension. I'm having issues when trying to add or update rows where one of the columns in the query is of type geometry. Retool lists the column as USER-DEFINED.

I'm using SQL mode for the queries as the GeoJSON strings need to be converted to the geometry format. For example:

INSERT INTO sometable (id, geom) VALUES (1, ST_GeomFromGeoJSON('{"type":"LineString","coordinates":[[149.18525902037214,-35.29515584261945],[149.18525902037217,-35.304149046256704]]}');

The data is coming via a custom component as JSON which is then used to construct the query:

INSERT INTO sometable (id, geom) VALUES ( {{component.model.id}}, ST_GeomFromGeoJSON('{{component.model.geometry}}'));

When run I get the error message "Unknown GeoJSON type". When I inspect the query state and extract the generated query and paste on the psql command line it works fine. Is retool trying to type the data in some way and rejecting the query?

I've tried the GUI query mode and provide a complete value (e.g. ST_GeomFromGeojson(...)) but retool appears to quote the query value which the database doesn't like.

It's as if I need a method in retool where I can tell retool to ignore the column and not to do any type checking, i.e. assuming it retool and not me.

The snapshot below is an actual insert which when cut-n-paste into psql runs fine.

retool
retool1028×296 84.2 KB

Thanks for your help.
Pete.

2

Turns out what you see is not what you get. The generated query is not actually what retool is executing, but rather a straight substitution of the values it has. Because of what was in the query status "query" field I was mistakenly passing the quoted values to satisfy Postgres and hence the cut-n-paste query worked. When I removed the quotes around the geometry value I got a new foreign key constraint error which made me think that I needed to remove all quoted strings and let retool worry about that. Problem solved.

3