Re: BUG #13160: Incorrect escaping of quotes in JSON export

From: Marko Tiikkaja <marko(at)joh(dot)to>
To: me(at)andrewray(dot)me, pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #13160: Incorrect escaping of quotes in JSON export
Date: 2015-04-27 16:12:48
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-bugs

On 4/25/15 9:53 PM, me(at)andrewray(dot)me wrote:
> Postgres escapes quotes incorrectly when creating a JSON export.
> UPDATE models SET column='"hello"' WHERE id=1;
> COPY (SELECT row_to_json(models)
> FROM (SELECT column FROM shaders WHERE id=1) shaders)
> TO '/output.json';
> The contents of output.json:
> {"column":"\\"hello\\""}
> You can see that the quotes are escaped improperly and it creates invalid

It's not supposed to create valid JSON. It's supposed to generate
something which can be COPYed back into a database. From the COPY
documentation page:

Backslash characters (\) can be used in the COPY data to quote data
characters that might otherwise be taken as row or column delimiters.
In particular, the following characters must be preceded by a
backslash if they appear as part of a column value: backslash itself,
newline, carriage return, and the current delimiter character.

> It should be:
> {"column":"\"hello\""}

That doesn't COPY back in:

=# \copy qwr from /tmp/foo
ERROR: invalid input syntax for type json
DETAIL: Token "hello" is invalid.
CONTEXT: JSON data, line 1: {"column":""hello...
COPY qwr, line 1, column a: "{"column":""hello""}"

COPY currently doesn't support what you're trying to do. I suggest
using a simple program which connects to the database and writes the
result column into a file verbatim.


In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2015-04-27 16:23:21 Re: BUG #13168: DROP DATABASE does not clean up all references
Previous Message Alvaro Herrera 2015-04-27 16:00:15 Re: BUG #13143: Cannot stop and restart a streaming server with a replication slot