postgresql 在字符串值中导入 CSV 和逗号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10001651/
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
Importing CSV and commas in string values
提问by zerkms
I use postgres 8.4 and currently trying to import a trivial CSV:
我使用 postgres 8.4，目前正在尝试导入一个简单的 CSV：
Here is a table:
这是一个表格：
CREATE TABLE public.sample (
a VARCHAR,
b VARCHAR
) WITHOUT OIDS;
and here is a csv file sample:
这是一个 csv 文件示例：
"foo","bar, baz"
The query
查询
COPY sample FROM '/tmp/sample.csv' USING DELIMITERS ',';
expected throws
预期投掷
ERROR: extra data after last expected column
CONTEXT: COPY sample, line 1: ""foo","bar, baz""
But, well, CSV parsing is not a rocket science and I would wonder if it is not possible to solve without reformatting the source csv.
但是，CSV 解析并不是一门火箭科学，我想知道如果不重新格式化源 csv 是否无法解决。
Any ideas?
有任何想法吗？
PS: the csv comes from 3rd party, I cannot change the format.
PS：csv 来自 3rd 方，我无法更改格式。
PPS: yes, I understand I could pre-process it (change the delimiter manually) before I import it
PPS：是的，我知道我可以在导入之前对其进行预处理（手动更改分隔符）
Final solution:
最终解决方案：
COPY sample FROM '/tmp/sample.csv' WITH DELIMITER ',' CSV;
Originally taken from https://stackoverflow.com/a/9682174/251311, and documentation page is http://www.postgresql.org/docs/8.4/static/sql-copy.html
最初取自https://stackoverflow.com/a/9682174/251311，文档页面是http://www.postgresql.org/docs/8.4/static/sql-copy.html
回答by Erwin Brandstetter
Clearly you have a CSV file while you try to import it as text format. Use:
很明显，当您尝试将其导入为文本格式时，您有一个 CSV 文件。利用：
COPY sample FROM '/tmp/sample.csv' FORMAT CSV;
The default delimiter for CSV format is the comma (,) anyway. More in the manual. Syntax is for current version 9.1. For PostgreSQL 8.4:
CSV 格式的默认分隔符,无论如何都是逗号 ( )。手册中有更多内容。语法适用于当前版本 9.1。对于 PostgreSQL 8.4：
COPY sample FROM '/tmp/sample.csv' CSV;
