是否可以在 CSV 格式的 Postgres COPY 命令中关闭报价处理? [英] Is it possible to turn off quote processing in the Postgres COPY command with CSV format?

问题描述

我有 CSV 文件，制表符分隔，字段未用引号括起来，其中字段数据可以包含单引号、双引号、管道和反斜杠等字符.

I have CSV files, tab-separated, fields not wrapped in quotes, where field data can contain characters like single quotes, double quotes, pipes and backslashes.

示例数据如下所示:

1       2       "ba$aReR       18

我想使用 COPY 语句将此数据导入 Postgres.

I want to import this data into Postgres using the COPY statement.

当我尝试使用

COPY <tablename> FROM  <filename> NULL AS '';

我收到一个错误 psql:-:1: ERROR: missing data for column 因为 Postgres 将反斜杠 + 制表符视为转义的制表符"而不是反斜杠后跟字段分隔符.

I get an error psql:-:1: ERROR: missing data for column because Postgres is treating the backslash + tab as an "escaped tab" instead of a backslash followed by the field separator.

所以我转而使用 COPY 运算符的CSV 格式"，如下所示:

So I switched to using the "CSV format" of the COPY operator, like so:

COPY <tablename> FROM <filename> WITH CSV DELIMITER E'	' NULL AS '';

现在有一个新错误 psql:-:1: ERROR: value too long for type character variables(254)

显然是因为它将字段 3 开头的双引号解释为字段换行符.

Apparently because it's interpreting the double-quote at the start of field 3 as the field wrapping character.

如何指定我的数据不被引用?

How can I specify that my data is NOT quoted at all?

推荐答案

解决方法(感谢 此评论！)

COPY <tablename> FROM <filename> WITH CSV DELIMITER E'	' QUOTE E'' NULL AS '';

所以基本上指定了一个不应出现在文本中的引号字符，但这非常难看.

So basically specifying a quote character that should never be in the text, but that's pretty ugly.

如果实际上有一种方法可以完全关闭报价处理，我会更喜欢它.

I'd much prefer it if there was in fact a way to turn off quote processing altogether.

这篇关于是否可以在 CSV 格式的 Postgres COPY 命令中关闭报价处理?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！