Inserting values to JSONB columns in PostgreSQL

[armingrudd]

Hi,

I’m trying to update a JSONB column in a PostgreSQL database but the node fails with this error:
column "errors" is of type jsonb but expression is of type character varying
There is no type mapping option for JSONB. I wonder how can I insert values in these columns?

@mlauber71, I tried to do this in Python with this code:

import psycopg2
import pandas as pd
df = pd.DataFrame(data=input_table_1)
connection = psycopg2.connect(user="test",password="1234",host="host",port="5432",database="db")
cursor = connection.cursor()
query = "UPDATE " + flow_variables['table'] + " SET errors = '" + df['errors'] + "' where id = " + df['id']
for i in query:
    cursor.execute(i)
connection.close()

the node executes successfully without any errors but nothing gets updated in the database. I tried putting ::json after df[‘errors’] in the code but still got nothing.

[Daniel_Weikert]

I think you are missing the .commit() after executing the cursor

[armingrudd]

Thank you @Daniel_Weikert,
Hmmm, It seems I missed it while I was trying different codes and since all my attention was on the query I didn’t notice the missing commit() function. Thank you again.
Yet, I wonder if it is possible to insert jsonb using DB nodes?

[armingrudd]

It seems that writing string or JSON to JSONB columns is also possible with sending the query directly using the DB SQL Executor node without any extra conversion.
Now I wonder even more why isn’t it possible with other DB nodes like DB Update.