json - postgresql json to columns error Character with value 必须转义

Question

我尝试从包含 json 行的表中加载一些数据。
有一个字段可以包含特殊字符，如\t 和\r，我想在新表中保留它们。

这是我的文件:

{"text_sample": "this is a\tsimple test", "number_sample": 4}

这是我的做法:

Drop table if exists temp_json;
Drop table if exists test;
create temporary table temp_json (values text);

copy temp_json from '/path/to/file';

create table test as (select 
        (values->>'text_sample') as text_sample,
        (values->>'number_sample') as number_sample
        from   (
           select replace(values,'\','\\')::json as values
           from   temp_json
       ) a);

我一直收到这个错误:

ERROR:  invalid input syntax for type json
DETAIL:  Character with value 0x09 must be escaped.
CONTEXT:  JSON data, line 1: ...g] Objection to PDDRP Mediation (was Re: Call for...

我需要如何转义这些字符？
非常感谢

Answer 1

如 Andrew Dunstan's PostgreSQL and Technical blog 中所述

In text mode, COPY will be simply defeated by the presence of a backslash in the JSON. So, for example, any field that contains an embedded double quote mark, or an embedded newline, or anything else that needs escaping according to the JSON spec, will cause failure. And in text mode you have very little control over how it works - you can't, for example, specify a different ESCAPE character. So text mode simply won't work.

所以我们必须转向CSV格式模式。

copy the_table(jsonfield) 
from '/path/to/jsondata' 
csv quote e'\x01' delimiter e'\x02';

在官方文档中sql-copy ，这里列出了一些参数:

COPY table_name [ ( column_name [, ...] ) ]
    FROM { 'filename' | PROGRAM 'command' | STDIN }
    [ [ WITH ] ( option [, ...] ) ]
    [ WHERE condition ]

where option can be one of:

    FORMAT format_name
    FREEZE [ boolean ]
    DELIMITER 'delimiter_character'
    NULL 'null_string'
    HEADER [ boolean ]
    QUOTE 'quote_character'
    ESCAPE 'escape_character'
    FORCE_QUOTE { ( column_name [, ...] ) | * }
    FORCE_NOT_NULL ( column_name [, ...] )
    FORCE_NULL ( column_name [, ...] )
    ENCODING 'encoding_name'

• 格式
  • 选择要读取或写入的数据格式:文本、csv(逗号分隔值)或二进制。默认为文本。
• 引用
  • 指定引用数据值时要使用的引用字符。默认是双引号。这必须是一个单字节字符。只有在使用 CSV 格式时才允许使用此选项。
• 分隔符
  • 指定用于分隔文件每一行(行)中的列的字符。默认为文本格式的制表符，CSV 格式的逗号。这必须是一个单字节字符。使用二进制格式时不允许使用此选项。
• 空
  • 指定表示空值的字符串。默认值为文本格式的\N(反斜杠-N)和 CSV 格式的未加引号的空字符串。对于不想区分空值和空字符串的情况，即使是文本格式，您也可能更喜欢空字符串。使用二进制格式时不允许使用此选项。
• 标题
  • 指定文件包含标题行，其中包含文件中每一列的名称。输出时，第一行包含表中的列名，而输入时，忽略第一行。只有在使用 CSV 格式时才允许使用此选项。