Insert Jsonb into Postgresql Database


i need to insert some data into a Postgresql Database. I never had any problems with this kind of task but now i've got the problem, that one of the database columns is a jsonb column.

The database writer node gives me the error:

ERROR: column "data" is of type jsonb but expression is of type character varying

Is there a way for me to fix this?


Thank you :-)

1 Like

Can you convert your JSON column to a String?

I believe the problems is the other way round: the database column has the (non-standard) type "jsonb" whereas KNIME tries to insert the JSON as a string. I have not idea how a "jsonb" columnd should be filled officially. Maybe it is treated as a binary type?

1 Like

Thank you for your answers. For now i've changed the column to string. But later on i have to use the json column to be able to select parts of the json in my sql query.

Is ther a way to insert and cast the values? 

String query = "INSERT INTO table (json_field) VALUES (to_json(?::json))"

Fount on Stackoverflow

1 Like

I’m running into the same problem of trying to upsert a string into a jsonb column in postgresdb. Anyone figured out how to make it work?