Update jsonb field in PostgreSQL with json that contains single quote

Question

I'm using PostgreSQL v9.4.12 and I'm trying to update a jsonb column. I want to update the whole json object and not a specific key of the object.

I'm using a Python dict to store my object and before using it I'm using json.dumps() to transform it to a json formatted String. However, a value of the json is having a single quote ' that throws an psycopg2.ProgrammingError: syntax error while trying to update.

So far, I've tried:

"UPDATE table "
"SET jsonb_column='{} ".format(json.dumps(new_data)) + ""
"WHERE id='12345'"

Note that new_data is my dict and jsonb_column is the name of the column holding the json data.

The error I'm getting:

psycopg2.ProgrammingError: syntax error at or near "s"

LINE 1: ...code": "BR3", "short_description": "This property's price is...

                                                  ^

I was assuming that json.dumps() escapes the single quote but doesn't seem that to be the case. Is there any solution to overcome this error?

Thanks in advance.

Answer 1

json is very fine with single quote, eg:

t=# select $${"short_description": "This property's price is..."}$$::jsonb;
                        jsonb
------------------------------------------------------
 {"short_description": "This property's price is..."}
(1 row)

so I assume you could try using dollar sign quotes, to avoid statement structuring exception with single quotes