问题描述
有效的JSON自然可以具有反斜杠字符:\.当您像这样在SQL语句中插入数据时:
Valid JSON can naturally have the backslash character: \. When you insert data in a SQL statement like so:
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复制到文件中并运行复制命令，则会得到:
But if I copy the JSON to a file and run the copy command I get:
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 和我被迫使用双反斜杠.就是这样，即文件内容为:
Seems like postgres is not processing the backslashes. I think because of http://www.postgresql.org/docs/8.3/interactive/sql-syntax-lexical.html andit I am forced to use double backslash. And that works, i.e. when the file contents are:
{"foo":"bar", "bam": "{\\"mary\\": \\"had a lamb\\"}" }
复制命令有效.但是期望对json数据类型进行特殊处理是否正确因为上述毕竟不是有效的json.
The copy command works. But is it correct to expect special treatment for json data typesbecause afterall above is not a valid json.
推荐答案
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';
这篇关于将具有副本的有效json插入到postgres表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！