On 4/25/15 9:53 PM, me@andrewray.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
> JSON.
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.
.m