Postgres - Copy (Stripped Double Quotes)

Question

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.

I have attempted to resolve the issue base on info in http://www.postgresql.org/docs/8.3/interactive/sql-syntax-lexical.html but have had no success.

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"

Solution

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;

Comments

[Bit Bucket]

I quoted the embedded quotes as suggested which did the trick. I did not try the other suggestion since the first worked. I appreciate the help.

[wildplasser]

I am sorry. Because this kind of stuff is always between systems, one sometimes has to resort to sed scripts and worse. But a true DBA has to deal with this kind of shit. It is a fact of life, so it seems...

[Gumbatron]

If you have reasonable control or at least knowledge over what characters will appear, you could choose a QUOTE character that won't appear in the text (such as '`'). I used a similar set up with unusual QUOTE and DELIMITER characters (` and ~ respectively) for reading a set of java .properties files (Key=value format) into a database.