将 CSV 文件导入 PostgreSQL 时忽略引号？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7376322/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Ignore quotation marks when importing a CSV file into PostgreSQL?
提问by grautur
I'm trying to import a tab-delimited file into my PostgreSQL database. One of the fields in my file is a "title" field, which occasionally contains actual quotation marks. For example, my tsv might look like:
我正在尝试将制表符分隔的文件导入到我的 PostgreSQL 数据库中。我的文件中的一个字段是“标题”字段，它偶尔包含实际的引号。例如，我的 tsv 可能如下所示：
id title
5 Hello/Bleah" Foo
(Yeah, there's just that one quotation mark in the title.)
（是的，标题中只有一个引号。）
When I try importing the file into my database:
当我尝试将文件导入我的数据库时：
copy articles from 'articles.tsv' with delimiter E'\t' csv header;
I get this error, referencing that line:
我收到此错误，引用该行：
ERROR: unterminated CSV quoted field
How do I fix this? Quotation marks are never used to surround entire fields in the file. I tried copy articles from 'articles.tsv' with delimiter E'\t' escape E'\\' csv header;but I get the same error on the same line.
我该如何解决？从不使用引号将文件中的整个字段括起来。我试过了，copy articles from 'articles.tsv' with delimiter E'\t' escape E'\\' csv header;但我在同一行上遇到了同样的错误。
采纳答案by wildplasser
Tab separated is the default format for copy statements. Treating them as CSV is just silly. (do you take this path just to skip the header ?)
制表符分隔是复制语句的默认格式。将它们视为 CSV 是愚蠢的。（你走这条路只是为了跳过标题吗？）
copy articles from 'articles.tsv';
does exactly what you want.
做你想要的。
回答by daxelrod
Assuming the file never actually tries to quote its fields:
假设文件从未真正尝试引用其字段：
The option you want is "with quote", see http://www.postgresql.org/docs/8.2/static/sql-copy.html
您想要的选项是“带引号”，请参阅http://www.postgresql.org/docs/8.2/static/sql-copy.html
Unfortunately, I'm not sure how to turn off quote processing altogether, one kludge would be to specify a character that does not appear in your file at all.
不幸的是，我不确定如何完全关闭报价处理，一个麻烦是指定一个根本没有出现在您的文件中的字符。
回答by andilabs
I struggled with the same error and a few more. Finally gathering knowledge from few SO questions I came up with the following setup for making COPY TO/FROM successful even for quite sophisticated JSON columns:
我在同样的错误和更多错误中挣扎。最后从几个 SO 问题中收集知识，我想出了以下设置，即使对于非常复杂的 JSON 列，也可以使 COPY TO/FROM 成功：
COPY "your_schema_name.yor_table_name" (your, column_names, here)
FROM STDIN WITH CSV DELIMITER E'\t' QUOTE '\b' ESCAPE '\';
--here rows data
\.
the most important parts:
最重要的部分：
QUOTE '\b'- quote with backspace (thanks a lot @grautur!)DELIMITER E'\t'- delimiter with tabsESCAPE '\'- and escape with a backslash
QUOTE '\b'- 用退格引用（非常感谢@grautur！）DELIMITER E'\t'- 带制表符的分隔符ESCAPE '\'- 并用反斜杠转义
