cannot copy CSV into postgreSQL table : timestamp column won't accept the empty string

Question

I want to import a CSV file into version 9.2 but the CSV file has double-quote double-quote in the final column position to represent a NULL value:

"2","1001","9","2","0","0","130","","2012-10-22 09:33:07.073000000",""

which is mapped to a column of type Timestamp. postgreSQL doesn't like the "". I've tried to set the NULL option but maybe I'm not doing it correctly? I've tried NULL as '"" and NULL '' and NULL as '' and NULL "" but without success; here's my command:

COPY SCH.DEPTS 
FROM 'H:/backups/DEPTS.csv' 
WITH (
 FORMAT CSV,
 DELIMITER ',' ,
 NULL  '',
 HEADER TRUE,
 QUOTE   '"' 
 )

but it fails with an error:

ERROR: invalid input syntax for type timestamp: ""

CONTEXT: COPY depts, line 2, column expirydate: ""

P.S. Is there a way to specify the string representation of Booleans to the COPY command? The utility that produced the CSVs (of which there are many) used "false" and "true".

Koa

Answer 1

The empty string ("") isn't a valid timestamp, and COPY doesn't appear to offer a FORCE NULL or FORCE EMPTY TO NULL mode; it has the reverse, FORCE NOT NULL, but that won't do what you want.

You probably need to COPY the data into a table with a text field for the timestamp, probably an UNLOGGED or TEMPORARY table, then use an INSERT INTO real_table SELECT col1, col, col3, NULLIF(tscol,'') FROM temp_table;.

COPY should accept true and false as booleans, so you shouldn't have any issues there.

Alternately, read the CSV with a simple Python script and the csv module, and then use psycopg2 to COPY rows into Pg. Or just write new cleaned up CSV out and feed that into COPY. Or use an ETL tool that does data transforms like Pentaho Kettle or Talend.

Fissile

this export format from MSFT is unlike any CSV I've seen before, in that it quotes all values regardless of whether they're numeric or strings. Almost as if they were making the export less useful on purpose ;-) It's sending "true" not true as well. Time to clean up the exports. Thanks for the suggestions of Kettle and Talend. – Koa

Answer 2

This still seems to be an issue 5 years later. I ran into this issue today running PostgreSQL 9.6.8. As a workaround before running the COPY command, I use sed to replace all occurrences of "" with null and then add NULL as 'null' to my COPY command i.e.

sed -i 's/""/null/g' myfile.csv

PGPASSWORD=<pwd> psql -h <host> -p <port> -d <db> -U <user>
-c "\copy mytable from myfile.csv WITH CSV DELIMITER ',' QUOTE '\"' ESCAPE '\\' NULL as 'null';"

Crepitate