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 :-)
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?
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
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?