Today I tried inserting some geographic data into a PostGIS-PostgreSQL database using KNIME. I have x- and y-column in my KNIME workflow and I want to combine them into one "coordinate" column of the PostGIS Type "Point" in my database.
That column accepts only spatial data (geometry data) of type "Point". This is a special PostGIS datatype which can be constructed from an x- and y- coordinate using the function "ST_Point(x,y)". A respective query would look like
UPDATE geotable SET coordinate = ST_Point(1,2) WHERE id = 1
or with parameters from my KNIME input table, respectively:
UPDATE geotable SET coordinate = ST_Point($x$, $y$) WHERE id = $id$
Now of course, that query should be parameterized with the values from my table in KNIME. That should be possible with a parameterized query. Attempting that results in an error from PSQL, that not all parameters were initialized (Execute failed: org.postgresql.util.PSQLException: Für den Parameter 1 wurde kein Wert angegeben.)
It even fails with fixed values like in the first example, due to an empty result (fails with "org.postgresql.util.PSQLException: Die Abfrage lieferte kein Ergebnis.").
If I add a "dummy select" like suggested elsewhere (as soon as I find it I will add the link):
SELECT * FROM geotable; UPDATE geotable SET coordinate = ST_Point(1,2) WHERE id = 1;
The sql query jields multiple result sets (org.postgresql.util.PSQLException: Die Abfrage ergab mehrere ResultSets).
Actually it would be nice if writing such datatypes would be possible on the Database Writer node, for example by allowing a custom data type instead of string, where the user is responsible for adding the type decorations. Is there any option to accomplish what I am looking for, which I have overlooked so far?