psql insert json with double quotes inside strings

Question

I'm trying to insert exiftool generated JSON into postgresql via psql which appears valid. It appears somehow that having the escaped single quote and the escaped double quote are not working properly. I can't figure out how to properly escape the json. It appears that psql isn't handling the single quote escape properly as its booting the \" out to the psql instead of the query.

Given this table

create table test (exif jsonb);

These work:

test=> insert into test values ('{"a": 1, "b": "2"}');
INSERT 0 1
test=> insert into test values ('{"a": 1, "b": "2\""}');
INSERT 0 1
test=> select * from test;
     exif
----------------------
{"a": 1, "b": "2"}
{"a": 1, "b": "2\""}

But these don't

test=> insert into test values ('{"a": 1, "b": "1\' 2\""}');
Invalid command \""}');. Try \? for help.

test=> select '{"a": 1, "b": "1' 2\""}';
Invalid command \""}';. Try \? for help.

test=> select E'{"a": 1, "b": "1' 2\""}';
Invalid command \""}';. Try \? for help.

test=> select '{"a": 1, "b": "1\' 2\""}';
Invalid command \""}';. Try \? for help.

Any suggestions?

Answer 1

In a database command to escape a single quote you need to double it:

test=> insert into test values ('{"a": 1, "b": "1'' 2\""}');