Errors writing KNIME string to PostgreSQL uuid field

[ettobias]

I’ve tried to find a solution to my issue in these forums, via search engine, and anything else I can think of with no success, so I’m posting my first topic to see if I can get a solution here.

I am reading from a Postgres table in a “1.0” database, manipulating field names, adding a constant column value, and writing to a “2.0” database table. It looks to me like KNIME doesn’t like, or know how to handle the Postgres uuid data type as I get the following error:

ERROR DB Insert 0:13 Execute failed: Batch entry 0 INSERT INTO “###”."###" (“MyPK”, “###”, “###”, “###”, “###”) VALUES (‘2a6210c6-971d-4f1a-8752-12daa4b7e3b4’,’###’,NULL,NULL,3) was aborted: ERROR: column “MyPK” is of type uuid but expression is of type character varying
Hint: You will need to rewrite or cast the expression.

(I’ve replaced table and field names in the error message, with the exception of the uuid/guid I’m trying to insert into.)

I can paste the SQL statement directly into PgAdmin and it works fine, but not in KNIME using a DB Insert node. I’ve also tried adding the braces in the string (i.e., “{}”), but that doesn’t help.

It looks to me like this is an issue between JDBC and SQL, based on some articles I’ve found, but all of the solutions make changes inside Java… not inside KNIME.

How do I handle this so the string-encoded uuid will insert?

[mlauber71]

@ettobias welcome to the KNIME forum

The solution might very well look something like this:

Insert JSON value into Postgresql database table

Hello Tokbanov, in order to write a KNIME JSON cell into a Postgres JSON column you need to do the following: In the PostgreSQL Connector node you need to add the speciy the JDBC Parameter stringtype=unspecified [image] and you need to add the following mapping JSON->String->Varchar to the Output Type Mapping tab [image] Once this is done you should be able to write JSON cells into JSON columns in the database. When you want to read the data back into KNIME you can use the DB Type…

Since KNIME might not have a data type uuid you would have to store it as a string and when insertig it into Postgres make sure it is recognized as uuid.

You might also upload the KNIME file into a temporary Postgres table and then convert it into uuid with somethink like:

CAST(<your_variable> AS uuid) AS <your_target_variable>

I might see if I can come up with an example how to do that

[mlauber71]

A solution could be something like this. You upload the file with the UUID into a emporary table and then insert it into the target table

The CAST() command would convert it into a uuid

Example how to use H2 database with a uuid unique identifier – mlauber71

[bruno29a]

Hi @ettobias , the solution presented by @mlauber71 is an excellent one, but I just wanted to comment on what you said:

@ettobias

I can paste the SQL statement directly into PgAdmin and it works fine, but not in KNIME using a DB Insert node.

Well, you’re not really comparing the same thing here. The DB Insert node will read from a Knime table, and therefore will try to match the column types, as opposed to executing an INSERT statement via PgAdmin where it’s not having to compare the types.

If you really want to test what you said, try to execute the same INSERT statement you are executing in PgAmin in Knime using the DB SQL Executor node. Then you will be comparing apple to apple.

But for your task, I would load your data into a temp table, be it using an H2 virtual instance (as @mlauber71 's suggestion), or on your Postgres server, and then do INSERT SELECT casting as uuid INTO your_destination_table via the DB SQL Executor mentioned above.

[ettobias]

Ah, great idea. I hate feeling like a noobie at software, even if I am new to it. I’m the master of several other pieces of software, so it annoys me to no end to have to ask noobie questions.

I’ll check this out for sure and come back with my results. I did think about the data type mapping, but couldn’t figure out how to get it to work. A temp table I definitely should have thought of… but lesson learned. Wish me luck!

[ettobias]

I get that it is comparing apples and oranges. My point was that the syntax was correct. I realize that there are additional layers between KNIME and Postgres. I was more noting that at the very least I made sure the syntax that was being generated was correct and I wasn’t making some bonehead mistake that was breaking the SQL statement.

And DB SQL Executor node is also something I probably should have thought of. As I said in my prior response to @mlauber71, I hate being a noobie in software, but we all have to put in our time. Thank you for the response and pointer. I’ll try it out too.

[bruno29a]

No worries @ettobias . Let us know what you find out, we’ll be interested

[ettobias]

Oh… and I absolutely love your ManU logo, @bruno29a , if we’re permitted a single sentence of non-support type conversation in the forums.

[mlauber71]

@bruno29a

But for your task, I would load your data into a temp table, be it using an H2 virtual instance (as @mlauber71 's suggestion), or on your Postgres server, and then do INSERT SELECT casting as uuid INTO your_destination_table via the DB SQL Executor mentioned above.

I think in oder for this solution to work it would have to happen on the same (Postgres) server as the final table. I just used H2 because one can use it in-memory and stand-alone so as to create a complete KNIME example - and it has a varaible type of uuid.

[bruno29a]

Hi @mlauber71 , that is correct, it would have to be on the same postgres server (which should not be an issue). I’m just not sure if we can cast the type uuid from postgres in H2, so that is why I prefer dealing with Postgres in this case.

[mlauber71]

@ettobias even after years of working with KNIME I still learn something new every day (never used uuid with KNIME before …). That is part of the joy of KNIME

And often there are several ways to solve a problem with KNIME since it is a platform and can integrate a lot of code and functions.

The DB / SQL part is designed as to work with a lot of databases using the same surface so people would have an easier time. Of course there are specific settings and functions in individual DB systems that sometimes are not fully covered by KNIME nodes in their initial setting. And you can use SQL via the special KNIME nodes or just write plain SQL and send it to the database. You still would have to take into consideration how the database does work. This is especially true if you use Big Data systems (the code would work but you are better off if you take the inner workings of eg. Cloudera into consideration).

With KNIME you come up with a working solution, and next time you might think of a better one (but thank to backward compatibility your old solution will still work).

And also this forum and community is there to help out. So just enjoy using KNIME and asking challenging questions.

[ettobias]

So it turns out this is much simpler than expected. The other forum article you sent me to @mlauber71 gave a suggestion that fixed it without needing anything further. It turns out that JDBC is explicitly casting the GUID as a string in the prepared statement it sends to Postgres.

By setting the “stringtype” JDBC parameter to “unspecified” the prepared statement sends the parameter values through without strongly typing them, so Postgres then interprets the string based on its format…which looks like a GUID/UUID, so Postgres runs the insert and casts it to uuid.

I may still try out the other suggestions just to learn more about KNIME, but for those looking for a solution to Postgres and GUID/UUID handling in KNIME, just add “stringtype=undefined” in the JDBC parameter tab of the connection.

[bruno29a]

That was quite very much simpler indeed @ettobias

Thank you for sharing.