CODEWITHSUNDEEP
jsonpostgresqlpsqljsonb
ruckc
ruckc

Reputation: 543

psql insert json with double quotes inside strings

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?

Upvotes: 11

Views: 21703

Answers (3)

jeb
jeb

Reputation: 173

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

Upvotes: 2

Jorge Campos
Jorge Campos

Reputation: 23381

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

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

Upvotes: 13

ruckc
ruckc

Reputation: 543

This is how to do escape the single quote properly:

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

Upvotes: 3

Related Questions