创建
JSON导出时,Postgres会错误地引用引号.请注意以下更新中的双引号…
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';
output.json的内容：
{"column":"\\"hello\\""}
您可以看到引号被不正确地转义,并且它会创建无效的JSON.
它应该是：
{"column":"\"hello\""}
解决方法
这与JSON无关.它是关于COPY命令中文本格式(默认)处理反斜杠的方式.从
the PostgreSQL documentation – COPY开始：
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.
(强调我的.)
您可以使用CSV格式并将引号字符从doublequote更改为其他内容来解决此问题.
展示：
SELECT row_to_json(row('"hello"'))
| "{"f1":"\"hello\""}" |
COPY (SELECT row_to_json(row('"hello"'))) TO '/output.json';
| {"f1":"\\"hello\\""} |
COPY (SELECT row_to_json(row('"hello"'))) TO '/output.json' CSV QUOTE '$';
| {"f1":"\"hello\""} |
