Insert Jsonb into Postgresql Database

KNIME Analytics Platform
1

Hi,

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
2

Can you convert your JSON column to a String?

3

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
4

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 http://stackoverflow.com/questions/35844138/how-can-i-insert-json-object-into-postgres-using-java-preparedstatement

1 Like
5

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?