Home How to handle nested quota for pg insertion
Reply: 1

How to handle nested quota for pg insertion

TieDad
1#
TieDad Published in 2017-12-07 11:48:13Z

I have a table two info two fields:

create table t (
   id  serial NOT NULL PRIMARY KEY,
   info jsonb
)

When I try to insert a row:

insert into t (info) values ('{"a": "b", "c": "c\"aa\"c"}'::jsonb)

I got an error:

Token "aa" is invalid.

How to solve this issue?

Vao Tsun
2#
Vao Tsun Reply to 2017-12-07 12:44:27Z

cant reproduce:

t=# create table t (
t(#    id  serial NOT NULL PRIMARY KEY,
t(#    info jsonb
t(# );
CREATE TABLE
t=# insert into t (info) values ('{"a": "b", "c": "c\"aa\"c"}'::jsonb);
INSERT 0 1
t=# select * from t;
 id |            info
----+-----------------------------
  1 | {"a": "b", "c": "c\"aa\"c"}
(1 row)

unless I change standard_conforming_strings to off

https://www.postgresql.org/docs/current/static/runtime-config-compatible.html

standard_conforming_strings (boolean) This controls whether ordinary string literals ('...') treat backslashes literally, as specified in the SQL standard. Beginning in PostgreSQL 9.1, the default is on (prior releases defaulted to off). Applications can check this parameter to determine how string literals will be processed. The presence of this parameter can also be taken as an indication that the escape string syntax (E'...') is supported.

with backslash taken as not sting, but escape char - json structure is broken, you see it in error message:

Token "aa" is invalid.

In order to insert valid json, you have to escape backspaces themselves:

t=# set standard_conforming_strings  to off;
SET
-- now when I have same settings as you:
t=# insert into t (info) values (e'{"a": "b", "c": "c\\"aa\\"c"}'::jsonb);
INSERT 0 1
t=# select * from t;
 id |            info
----+-----------------------------
  5 | {"a": "b", "c": "c\"aa\"c"}
(1 row)
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.309779 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO