Write to table with user defined type

KNIME Analytics Platform
1

Hi,

I've recently started using KNIME and although different, it's a very impressive tool.

I've got a question though: I have a table in Postgres that uses a user defined type. In this case, it's a type for gender - the options are 'M', 'F','U'. KNIME has the column defined as string (which is fine in principle). However, the table writer complains that "ERROR: column "gender" is of type o_wh.geslacht but expression is of type character varying". I cannot seem to change the columntype to ENUM. What is even more weird: I get the insert-statement returned (not strange) and when I execute that statement in PGAdmin, it inserts the record without a problem. It looks like KNIME is doing a final check on metadata (but it's wrong in its assumptions)... Is there a way to just execute the inserts without KNIME (partially) checking on metadata?

What to do?

Kind regards,

Raoul

2

You should be able to get around such things by using the sql execute node instead.

Use the postgresql enum-casting syntax in your SQL. So if you would have an insert statement like this:

  insert into xx(gender) values('M')

that should become

  insert into xx(gender) values('M'::geslacht)

 

Then get your values in the sql instead of hardcoding the value and use the sql inject and or sql executor to get it going.

3

Hi Ellert,

thanks - I'll give it a try!