Cannot copy CSV to postgreSQL table: timestamp column does not accept empty row

Question

I want to import a CSV file into version 9.2, but the CSV file has a double quote with a double quote at the end position of the column to represent NULL:

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

which maps to a column of type Timestamp. postgreSQL doesn't like "". I tried setting the NULL parameter, but maybe I am not doing it right? I tried NULL as '"" and NULL '' and NULL as '' and NULL "" , but to no avail; here is my command:

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

but it is not with an error:

ERROR: Invalid input syntax for timestamp type: "

CONTEXT: COPY division, row 2, expirydate column: ""

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

Answer 1

An empty string ("") is not a valid timestamp, and COPY does not offer FORCE NULL or FORCE EMPTY TO NULL mode; it has the flip side, FORCE NOT NULL , but it will not do what you want.

You probably need COPY data in a table with a text field for a timestamp, possibly a UNLOGGED or TEMPORARY table, and then use 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 a problem.

Alternatively, read CSV with a simple Python script and csv module, and then use the psycopg2 to COPY lines in Pg. Or just write a new cleaned CSV and submit it to COPY . Or use an ETL tool that converts data like Pentaho Kettle or Talend.