将有效的json复制插入到postgres表中

Question

有效的 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)

事情运转良好。

但是，如果我将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"}" }"

看起来postgres没有处理反斜杠。我想这是因为http://www.postgresql.org/docs/8.3/interactive/sql-syntax-lexical.html，所以我被迫使用双反斜杠。这样就可以了，也就是说，当文件内容为：

{"foo":"bar", "bam": "{\\"mary\\": \\"had a lamb\\"}" }  

复制命令是有效的。但期望为 JSON 数据类型提供特殊处理是否正确，毕竟上面不是有效的 JSON。

- Sid

---

请参考 https://stackoverflow.com/questions/52334512/postgres-copy-with-command-strips-escapes-from-json-file-also-loads-each-line-a/52355439#52355439。我认为这个解释了如何解决这个问题。 - mark d drake

这个回答解决了你的问题吗？如何将JSON文件导入PostgreSQL？ - tripleee

Answer 1

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

- Vlad Purga

---

请在回答中提供更多关于您发布的链接和代码片段的上下文。如何回答 - AgataB

这是在撰写本文时，对于 OP 的问题：“如何从文件系统中读取 json，而不必 应用特殊的转义语法（从而使 json 无效）”，的正确答案。不幸的是，由于负投票和低 SO 声望，大多数用户会跳过此答案。回答可以更好，但显然只是来自非 SO 用户的路过回答... - virtualeyes

Answer 2

PostgreSQL的默认批量加载格式是text，是一种以制表符分隔的标记语言。它需要转义反斜杠，因为它们对于（例如）\N空占位符有特殊含义。

观察PostgreSQL生成的内容：

regress=> COPY foo TO stdout;
{"foo":"bar", "bam": "{\\"mary\\": \\"had a lamb\\"}" }

这并不是只有 JSON 才需要特殊处理，所有字符串都需要。举个例子，一个字符串（包括 JSON）可能包含制表符。这些字符必须被转义，以防止它们被视为另一个字段。

你需要适当地转义生成的输入数据。通常情况下，使用 PostgreSQL 特定的 text 格式会更加困难，你可以使用 format csv 并使用能够正确转义的 CSV 写入工具进行编写。

- Craig Ringer

---

我不理解这个答案。具体来说，为什么 PostgreSQL 没有进行转义？这听起来像是 PostgreSQL 接受一个制表符分隔的文件（其中可能包含 JSON），而不是纯粹的 JSON 文件。 - Chris Stryczynski

@ChrisStryczynski 没错，它是TSV格式。COPY不用于加载单个字段，而是用于加载表。 - Craig Ringer