postgresql 将带有副本的有效 json 插入 postgres 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24190039/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inserting valid json with copy into postgres table
提问by Sid
Valid JSON can naturally have the backslash character: \. When you insert data in a SQL statement like so:
有效的 JSON 自然可以有反斜杠字符：\。当您像这样在 SQL 语句中插入数据时：
sidharth=# create temp table foo(data json);
CREATE TABLE
sidharth=# insert into foo values( '{"foo":"bar", "bam": "{\"mary\": \"had a lamb\"}" }');
INSERT 0 1
sidharth=# select * from foo;
data
\-----------------------------------------------------
{"foo":"bar", "bam": "{\"mary\": \"had a lamb\"}" }
(1 row)
Things work fine.
一切正常。
But if I copy the JSON to a file and run the copy command I get:
但是如果我将 JSON 复制到一个文件并运行复制命令，我会得到：
sidharth=# \copy foo from './tests/foo' (format text);
ERROR: invalid input syntax for type json
DETAIL: Token "mary" is invalid.
CONTEXT: JSON data, line 1: {"foo":"bar", "bam": "{"mary...
COPY foo, line 1, column data: "{"foo":"bar", "bam": "{"mary": "had a lamb"}" }"
Seems like postgres is not processing the backslashes. I think because of http://www.postgresql.org/docs/8.3/interactive/sql-syntax-lexical.htmland it I am forced to use double backslash. And that works, i.e. when the file contents are:
似乎 postgres 没有处理反斜杠。我认为是因为http://www.postgresql.org/docs/8.3/interactive/sql-syntax-lexical.html并且我被迫使用双反斜杠。这有效，即当文件内容为：
{"foo":"bar", "bam": "{\"mary\": \"had a lamb\"}" }
The copy command works. But is it correct to expect special treatment for json data types because afterall above is not a valid json.
复制命令有效。但是期望对 json 数据类型进行特殊处理是否正确，因为毕竟以上不是有效的 json。
回答by Vlad Purga
http://adpgtech.blogspot.ru/2014/09/importing-json-data.html
http://adpgtech.blogspot.ru/2014/09/importing-json-data.html
copy the_table(jsonfield)
from '/path/to/jsondata'
csv quote e'\x01' delimiter e'\x02';
回答by Craig Ringer
PostgreSQL's default bulk load format, text, is a tab separated markup. It requires backslashes to be escaped because they have special meaning for (e.g.) the \Nnull placeholder.
PostgreSQL 的默认批量加载格式text，是一个制表符分隔的标记。它需要对反斜杠进行转义，因为它们对（例如）\N空占位符具有特殊含义。
Observe what PostgreSQL generates:
观察 PostgreSQL 生成的内容：
regress=> COPY foo TO stdout;
{"foo":"bar", "bam": "{\"mary\": \"had a lamb\"}" }
This isn't a special case for json at all, it's true of any string. Consider, for example, that a string - including json - might contain embedded tabs. Those must be escaped to prevent them from being seen as another field.
这根本不是 json 的特例，任何字符串都是如此。例如，考虑一个字符串——包括 json——可能包含嵌入的选项卡。必须将它们转义以防止它们被视为另一个领域。
You'll need to generate your input data properly escaped. Rather than trying to use the PostgreSQL specific textformat, it'll generally be easier to use format csvand use a tool that writes correct CSV, with the escaping done for you on writing.
您需要生成正确转义的输入数据。与尝试使用 PostgreSQL 特定text格式相比，它通常更易于使用format csv和使用编写正确 CSV 的工具，并在编写时为您完成转义。
