postgresql Postgres - 复制（去除双引号）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9417916/
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
Postgres - Copy (Stripped Double Quotes)
提问by Bit Bucket
I am using Postgres 8.4.4 copy, http://www.postgresql.org/docs/8.4/static/sql-copy.html, to import CSV data into my database. Some of the values in my source data contain double-quotes which are getting stripped upon insertion whereas when I do an INSERT or UPDATE statement via psql for testing the double-quotes are retained. Maybe giving a clue as to what is going on, some values also contain commas which are retained as needed.
我正在使用 Postgres 8.4.4 副本http://www.postgresql.org/docs/8.4/static/sql-copy.html将 CSV 数据导入我的数据库。我的源数据中的一些值包含双引号，它们在插入时会被剥离，而当我通过 psql 执行 INSERT 或 UPDATE 语句以测试双引号时，双引号被保留。也许提供有关正在发生的事情的线索，某些值还包含根据需要保留的逗号。
I have attempted to resolve the issue base on info in http://www.postgresql.org/docs/8.3/interactive/sql-syntax-lexical.htmlbut have had no success.
我试图根据http://www.postgresql.org/docs/8.3/interactive/sql-syntax-lexical.html 中的信息解决问题，但没有成功。
The copy command I'm using is:
我使用的复制命令是：
copy my_table (field_1, field_2, field_3 ...) from '/tmp/source.csv' with csv
The source data is double-quoted with comma separator. This can be changed if necessary...
源数据用逗号分隔符用双引号引起来。如有必要，这可以更改...
"value","another value","this is "another" value","no more, thanks"
“价值”，“另一个价值”，“这是“另一个”价值”，“没有了，谢谢”
回答by wildplasser
You'll have to quote the embedded quotes. Default is double them, so your data should be:
您必须引用嵌入的引号。默认是他们的两倍，所以你的数据应该是：
"value","another value","this is ""another"" value","no more, thanks"
The other way is to work unquoted (but you'll have to quote the commas, if any), like
另一种方法是不加引号地工作（但你必须引用逗号，如果有的话），比如
value,another value,this is "another" value,no more, thanks
UPDATE: This works, but you'll have to make sure the embedded '"'s are quoted (in this case by adding a backslash)
更新：这有效，但您必须确保引用嵌入的“”（在这种情况下通过添加反斜杠）
DROP TABLE tmp.my_table CASCADE;
CREATE TABLE tmp.my_table
( field_1 varchar
, field_2 varchar
, field_3 varchar
, field_4 varchar
);
COPY tmp.my_table (field_1,field_2,field_3,field_4)
FROM STDIN
WITH CSV DELIMITER ',' QUOTE '"' ESCAPE '\'
;
"value","another value","this is \"another\" value","no more, thanks"
\.
;
SELECT * FROM tmp.my_table;
