带有命令条的Postgres COPY从JSON文件中转义(也将每一行作为单独的行加载) [英] Postgres COPY with command strips escapes from JSON file (Also loads each line as seperate row)

问题描述

如果我有以下json文件

If I have the following json file

C:\PostgresStage>type test2.json
{"key":"Hello \"World\""}

然后我尝试使用COPY命令将其加载到json或text列中，但最终得到无效的JSON，因为复制似乎从文件中删除了转义字符，如下所示

And I try to load it into a json or text column using the COPY command I end up with invalid JSON because copy appears to strip the escape characters from the file as can be seen below

postgres=# \d+ T1
                               Table "public.t1"
Column | Type | Collation | Nullable | Default | Storage  | Stats target | Description
--------+------+-----------+----------+---------+----------+--------------+-------------
 data   | text |           |          |         | extended |              |


postgres=# delete from T1;
DELETE 1
postgres=# copy t1 from 'c:\PostgresStage\test2.json';
COPY 1
postgres=# select  * from T1;
          data
-------------------------
 {"key":"Hello "World""}
 (1 row)


postgres=# select data::jsonb from T1;
ERROR:  invalid input syntax for type json
DETAIL:  Token "World" is invalid.
CONTEXT:  JSON data, line 1: {"key":"Hello "World...
postgres=#

反正有防止出现这种情况的方法.

Is there anyway to prevent this..

如果我有这样的漂亮" JSON

Also if I have 'pretty' JSON such as this

C:\PostgresStage>type test2.json
{
  "key":"Hello \"World\""
}

它被加载为3行.无论如何，是否可以使用复制将整个文件加载为一行.

It gets loaded as 3 rows. Is there anyway to use copy to load the entire file as one row.

最后还是可以使用COPY将参数传递给函数，而不是将行加载到表中

Finally is there anyway to use COPY to pass a parameter to a function rather than load a row into a table

推荐答案

对于不是漂亮打印"的JSON文档，例如整个文档是文件中的一行，则应使用hack

For JSON documents that are not "pretty printed", eg the entire document is a single line in the file, a hack is to use

 copy t1 from 'c:\PostgresStage\test2.json' csv quote e'\x01' delimiter e'\x02';

感谢 http://adpgtech.blogspot.com/2014 /09/importing-json-data.html

这篇关于带有命令条的Postgres COPY从JSON文件中转义(也将每一行作为单独的行加载)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！