I'm using Postgres 9.5 and I have a column ("info") of type 'json'... I'm trying to do this insert:
INSERT INTO table ( info )
VALUES (
'{"entry":"((\+)[0-9]+)"}'
)
but I get this error:
ERROR: invalid input syntax for type json
DETAIL: Escape sequence "\+" is invalid.
It's interpreting \+ as an escape sequence but I actually want that as part of my value.
In general,
jsonb_build_object to construct the object\.PostgreSQL isn't quoting this: it's just the JSON implimentation following RFC 7159.
A string begins and ends with quotation marks. All Unicode characters may be placed within the quotation marks, except for the characters that must be escaped: quotation mark, reverse solidus, and the control characters (U+0000 through U+001F). [... ] So, for example, a string containing only a single reverse solidus character may be represented more compactly as
"\\".
So it looks like this in the literal form.
CREATE TABLE tbl
AS
SELECT '{"entry":"((\\+)[0-9]+)"}'::jsonb AS info;
Dollar-quoting in PostgreSQL requires no-escapes, but it will not help here,
Notice that inside the dollar-quoted string, single quotes can be used without needing to be escaped. Indeed, no characters inside a dollar-quoted string are ever escaped: the string content is always written literally. Backslashes are not special, and neither are dollar signs, unless they are part of a sequence matching the opening tag.
So this will not work because \+ is not a valid string in JSON. It would work if we were not using a json type though.
SELECT '{"key":"\"}'::jsonb;
ERROR: invalid input syntax for type json
LINE 1: SELECT '{"key":"\"}'::jsonb;
^
DETAIL: Token ""\"}" is invalid.
CONTEXT: JSON data, line 1: {"key":"\"}
However, you can use to_jsonb() to JSON-escape the strings..
SELECT FORMAT( $${%s:%s}$$, to_jsonb(k), to_jsonb(v) )::jsonb
FROM ( VALUES
('key', '\' )
) AS t(k,v);
But, even this is a bad idea because if you have the keys and values you can use json_build_object,
SELECT jsonb_build_object( k, v )
FROM ( VALUES
('key', '\' )
) AS t(k,v);
info ->> 'entry' in your case). But if you retrieve the whole JSON (the info object, or the info -> 'entry' string), there is no "extra backslash", it's just the required escaping in the JSON value - which also has "extra quotes" around the string.I had a similar problem, found an easy solution which will remove escape character present in the jsonb field.
select (jsonb_field_name #>> '{}')::jsonb from table_name
Now you can see the data in a cleaner way
