是否可以使用CSV格式关闭Postgres COPY命令中的报价处理? [英] Is it possible to turn off quote processing in the Postgres COPY command with CSV format?
问题描述
我有CSV文件,制表符分隔的字段,不是包含在引号中,其中字段数据可以包含单引号,双引号,管道和反斜杠。
示例数据可能如下所示:
1 2ba $ aR\eR \ 18
我想使用COPY语句将此数据导入Postgres。
当我尝试使用
导入时 COPY< tablename> FROM< filename& ';
我收到一个错误 psql: - :1:ERROR:缺少列
的数据,因为Postgres正在将反斜杠+制表符视为转义制表符,而不是后跟字段分隔符的反斜杠。
因此,我切换到使用COPY运算符的CSV格式,如下所示:
COPY< tablename& filename> WITH CSV DELIMITER E'\'NULL AS'';
现在有一个新错误 psql: - :1:ERROR:值太长,类型字符变化(254)
显然,
如何指定我的数据是 =
解决方法(感谢此评论!)
COPY< tablename> FROM< filename> WITH CSV DELIMITER E'\''QUOTE E'\''NULL AS'';
所以基本上指定一个不应该出现在文本中的引用字符, p>
我更喜欢它,如果事实上有一个方法可以完全关闭报价处理。
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.
Sample data can look like this:
1 2 "ba$aR\eR\ 18
I want to import this data into Postgres using the COPY statement.
When I try to import this using
COPY <tablename> FROM <filename> NULL AS '';
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.
So I switched to using the "CSV format" of the COPY operator, like so:
COPY <tablename> FROM <filename> WITH CSV DELIMITER E'\t' NULL AS '';
Now there's a new error psql:-:1: ERROR: value too long for type character varying(254)
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?
Workaround (thanks to this comment!)
COPY <tablename> FROM <filename> WITH CSV DELIMITER E'\t' QUOTE E'\b' 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屋!