CODEWITHSUNDEEP
javajsonpostgresqljackson
Yair Zaslavsky
Yair Zaslavsky

Reputation: 4137

escaping of double quote with text type column at postgresql and json parsing via jackson


I have the following table defined at postgresql: 

        Column      |          Type          | Modifiers 
-----------------+------------------------+-----------
 id              | uuid                   | not null
 entity_snapshot | text                   | 
Indexes:
    "pk_id" PRIMARY KEY, btree (id)

I would like to store the following JSon string:

[ "org.test.MyUniqueId", {:   "uuid" : "f4b40050-9716-4346-bf84-8325fadd9876": } ]

for some testing, instead of doing this using Jackson, I try to type an SQL manually, and here is my problem - I can't seem to get ti right.
My current attempt is: 

insert into my_table(id,entity_snapshot) values ('d11d944e-6e78-11e1-aae1-52540015fc3f','[ \"org.test.MyUniqueId\", {:   \"uuid\" : \"f4b40050-9716-4346-bf84-8325fadd9876\": } ]');

Procuedure a record that looks like what I need when I select from the table, but when I try to use Jackson to parse it I get an error -

org.apache.commons.lang.SerializationException: org.codehaus.jackson.JsonParseException: Unexpected character (':' (code 58)): was expecting double-quote to start field name

Needless to say that if the same record is inserted via my java code , I can get it parsed and when it comes to looking at the record with human eyes, it looks the same to me.
Can you tell me where I am wrong in my SQL insert statement?

Upvotes: 3

Views: 11287

Answers (2)

user330315
user330315

Reputation:

Double quotes inside single quotes do not need any escaping. 

insert into my_table (id,entity_snapshot)
values 
(
   'd11d944e-6e78-11e1-aae1-52540015fc3f',
   '["org.test.MyUniqueId", {: "uuid" : "f4b40050-9716-4346-bf84-8325fadd9876": }]'
);

will work just fine: 

postgres=> create table my_table (id uuid, entity_snapshot text);
CREATE TABLE
Time: 34,936 ms
postgres=> insert into my_table (id,entity_snapshot)
postgres-> values
postgres-> (
postgres(>    'd11d944e-6e78-11e1-aae1-52540015fc3f',
postgres(>    '["org.test.MyUniqueId", {: "uuid" : "f4b40050-9716-4346-bf84-8325fadd9876": }]'
postgres(> );
INSERT 0 1
Time: 18,255 ms
postgres=>

Upvotes: 3

corsair
corsair

Reputation: 668

You can use Dollar-Quoted String Constants. Details are here Postgres documentation

In your case query should look like

insert into my_table(id,entity_snapshot) values ('d11d944e-6e78-11e1-aae1-52540015fc3f',$$[ "org.test.MyUniqueId", {:   "uuid" : "f4b40050-9716-4346-bf84-8325fadd9876": } ]$$);

Upvotes: 7

Related Questions