postgresql - 使用 Postgres COPY FROM 时如何转换十六进制字符？

Question

我正在使用 COPY FROM 将文件中的数据导入到 PostgreSQL 数据库表中。我文件中的一些字符串包含十六进制字符(主要是\x0d 和\x0a)，我希望使用 COPY 将它们转换为常规文本。
我的问题是它们被视为常规文本并且在字符串中保持不变。
如何转换十六进制值？

这是我的情况的一个简化示例:

-- The table I am importing to
CREATE TABLE my_pg_table (
    id serial NOT NULL,
    value text
);

COPY my_pg_table(id, data)
FROM 'location/data.file'
WITH CSV
DELIMITER '    ' -- this is actually a tab
QUOTE ''''
ENCODING 'UTF-8'

示例文件:

1    'some data'  
2    'some more data \x0d'  
3    'even more data \x0d\x0a'

注意:文件以制表符分隔。

现在，做:

SELECT * FROM my_pg_table

会得到包含十六进制的结果。

上下文的附加信息:

我的任务是从 sybase 表(数百个)导出数据并导入到 Postgres。我正在使用 UNLOAD 将数据导出到文件，如下所示:

UNLOAD
TABLE my_sybase_table
TO 'location/data.file'
DELIMITED BY '    ' -- this is actually a tab
BYTE ORDER MARK OFF
ENCODING 'UTF-8'

Answer 1

在我看来(出于某种我不明白的原因)十六进制仅在使用 FORMAT TEXT 时才会转换，而 FORMAT CSV 会将其视为 常规字符串。

解决我的问题:
因为我必须使用 TEXT 我不再有 QUOTE 选项，因此我不能再在我的文件中引用字符串。所以我需要一些不同格式的文件，并最终使用它从 sybase 导出我的表:

UNLOAD
    SELECT
    COALESCE(cast(id as long varchar), '(NULL)'),
    COALESCE(cast(data as long varchar), '(NULL)')
    FROM my_sybase_table
TO 'location/data.file'
DELIMITED BY '    ' -- still tab delimited
BYTE ORDER MARK OFF
QUOTES OFF
ENCODING 'UTF-8'

并将其导入到 postgres 中:

COPY my_pg_table(id, data)
FROM 'location/data.file'
DELIMITER '    ' -- tab delimited
NULL '(NULL)'
ENCODING 'UTF-8'

我使用了 (NULL)，因为我需要一种方法来区分空字符串和 null。我将每一列转换为 long varchar，以使我的批量导出/导入更加方便。

我仍然很想知道为什么在使用 FORMAT CSV 时十六进制无法转换。