DB Insert to PostgreSQL column of type interval

[wurz]

Hey everybody,

I want to insert data to a column in a PostgreSQL database which is of type interval. Up to now, I didn’t find the correct mapping to get this insert done. I always got the following message:
“ERROR: column “execution_time” is of type interval but expression is of type character varying”

I already tried it with the KNIME column types String (“00:00:56”) and Duration (56s).

Does anyone have a hint or a workaround?

Thanks in advance,
Best,
Johann

[MichaelRespondek]

Hi @wurz,

Unfortunately a CAST is not supported by the DB framework itself during an INSERT or usage of a specific Postgres type. PostgreSQL: Re: Inserting an Interval Using JDBC

The only possibility to achieve this would be the usage of the SQL Executor node and a self assembled INSERT INTO TABLE SQL-statement.

Best,
Michael

[kienerj]

Another not so nice solution is to make a trigger that does the cast so you can try inserting as string and let the trigger do the cast.
Of course it assumes you have that much control over the database and the error happens on insert and os not a check on KNIME side before actually doing anything.

[wurz]

Thanks for your answers!

The workaround with the DB SQL Executor node works, but it is a nasty one.

Best,
Johann

[wurz]

I just wanted to add my solution to update several entries with one query. I tried to build a minimal example. Hopefully this helps someone in the future!

My workaround is to build PostgreSQL value lists from the input data table and concatenate them to a combined values list. The trick is to add “::interval” to the future interval column there and not in the SQL query (took me a while to find this solution). For more details, check the workflow below!

Best,
Johann

PSQL_type_interval.knwf (13.2 KB)