使用ASCII 31字段分隔符作为Postgresql COPY定界符

Question

我们正在将Postgres 9.3中的数据导出为文本文件，以便Spark进行摄入。

我们希望使用ASCII 31字段分隔符作为分隔符，而不是\t，这样我们就不必担心转义问题。

我们可以在shell脚本中像这样做：

#!/bin/bash
DELIMITER=$'\x1F'
echo "copy ( select * from table limit 1) to STDOUT WITH DELIMITER '${DELIMITER}'" | (psql ...) > /tmp/ascii31

我们在思考，是否可以在 "纯" postgres 中指定一个不可打印的图形符号作为分隔符？

编辑：我们尝试使用 postgres 转义约定，参见http://www.postgresql.org/docs/9.3/static/sql-syntax-lexical.html

warehouse=> copy ( select * from table limit 1) to STDOUT WITH DELIMITER '\x1f';

并且接收到

ERROR:  COPY delimiter must be a single one-byte character

- jaegard

Answer 1

尝试在你想用作分隔符的序列前添加E。例如，使用E'\x1f'代替'\x1f'。如果没有E，则PostgreSQL会将'\x1f'视为四个单独的字符而不是十六进制转义序列，因此会出现错误消息。

有关更多信息，请参见PostgreSQL手册中的“带C样式转义字符的字符串常量”。

- Gregory Arenius

---

非常棒的答案。在Postgres脚本中，无论何时使用十六进制、正则表达式或特殊字符，都要始终始终在前面加上E。有时候可能不需要，但总会有那么一次没有明显原因地失败。 - pele88

Answer 2

从我的测试来看，以下两种都可以：

echo "copy (select 1 a, 2 b) to stdout with delimiter u&'\\001f'"| psql;

---

echo "copy (select 1 a, 2 b) to stdout with delimiter e'\\x1f'"| psql;

- bgoldst

---

哦，这两个在psql shell中都不起作用，都会产生“ERROR: COPY delimiter must be a single one-byte character”的错误。 - jaegard

嗯，奇怪。您运行的是哪个Postgres版本和操作系统？（我从“psql --version”获取到“psql（PostgreSQL）9.3.5”，运行在Windows 8.1上） - bgoldst

实际上，当你说“psql shell”时，你是指你复制了上面命令中的双引号文本，并将其粘贴到psql提示符下吗？这样是行不通的；双反斜杠是一个转义代码，在shell双引号字符串中将解析为单个反斜杠，然后被回显到 psql。在psql看到的代码中应该只有一个反斜杠。如果需要帮助，请告诉我。 - bgoldst

有人解决了吗？我想做同样的事情。当我尝试各种转义组合时，会出现 ERROR: invalid byte sequence for encoding "UTF8": 0x00 的错误。 - squarism

Answer 3

我从Actian Matrix（Amazon Redshift的一个分支，两者都是Postgres的衍生版本）中提取了一个小文件，使用ASCII字符代码30的符号“记录分隔符”。

unload ('SELECT btrim(class_cd) as class_cd, btrim(class_desc) as class_desc
FROM transport.stg.us_fmcsa_carrier_classes')
to '/tmp/us_fmcsa_carrier_classes_mk4.txt'
delimiter as '\036' leader;

这是一个在VI中查看该文件的示例：

C^^Private Property
D^^Private Passenger Business
E^^Private Passenger Non-Business

然后我通过sftp将此文件移动到托管PostgreSQL 9.5的机器上，并使用以下复制命令，似乎效果很好：

copy fmcsa.carrier_classes
from '/tmp/us_fmcsa_carrier_classes_mk4.txt'
delimiter u&'\001E'; 

每个Postgres的衍生版本，以及Postgres本身似乎更喜欢稍微不同的符号表示法。很遗憾我们没有一个统一的标准！

- Geoffrey Clark