Using PostgreSQL, how do I escape "\" in json columns?

Question

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.

Solution

In general,

• if you have keys and values use jsonb_build_object to construct the object
• if you're writing a literal JSON object to cast from string, then it must be proper; a proper JSON string requires escaping \.

Explanation

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);