Menu
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?
676
if you want to escape double quote in JSON use \\ to escape it.
In PostgreSQL, double quotes (like "a red dog") are always used to denote delimited identifiers. In this context, an identifier is the name of an object within PostgreSQL, such as a table name or a column name. Delimited identifiers are identifiers that have a specifically marked beginning and end.
Quotes and double quotes should be escaped using \.
In Postgresql, we can insert a single quote using the double single quote (”) or (E'\') to declare Posix escape string syntax.
In a database command to escape a single quote you need to double it:
test=> insert into test values ('{"a": 1, "b": "1'' 2\""}');
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
