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?

Delacroix

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\""}');

Matrilateral

The double quotes are escaped in the source, and json spec says they should be escaped. – Delacroix

Thanks for the follow up @Delacroix – Matrilateral

Answer 2

This is how to do escape the single quote properly:

test=> select '{"a": 1, "b": "1'' 2\""}';

Delacroix

Answer 3

Another option that I found sometimes useful is using $$ as begin and end of string. You still have to escape the double quote in the json.

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

Offal