Importing CSV with commas in string values
csv, postgresql, postgresql-8.4, postgresql-copy
Solution
Clearly, you have a CSV file while you try to import it as text format. For Postgres 9.1 or newer, use:
COPY sample FROM '/tmp/sample.csv' (FORMAT csv);
The default delimiter for CSV format is the comma (`,`) anyway. More in the manual. For PostgreSQL 8.4 or older:
COPY sample FROM '/tmp/sample.csv' CSV;
Problem
I am trying to import a trivial CSV to Postgres 8.4 database: Here is a table: ``` CREATE TABLE public.sample ( a VARCHAR, b VARCHAR ) WITHOUT OIDS; ``` Here is a CSV file sample: ``` "foo","bar, baz" ``` The query: ``` COPY sample FROM '/tmp/sample.csv' USING DELIMITERS ','; ``` Throws an exception: ``` ERROR: extra data after last expected column CONTEXT: COPY sample, line 1: ""foo","bar, baz"" ``` But, well, CSV parsing is not rocket science and I wonder if it is not possible to solve without reformatting the source CSV file. Input comes from a 3rd party, I cannot change the format. (I understand I could pre-process to change the delimiter before I import it.) 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