postgres csv date null import error

Question

I am importing data into a Postgres database. The table I am importing to includes a couple of columns with dates.

The CSV file I am uploading, however, has empty values for some of the date fields.

The table looks like this:

dot_number bigint,
legal_name character varying,
dba_name character varying,
carrier_operation character varying,
hm_flag character varying,
pc_flag character varying,
...
mcs150_date date,
mcs150_mileage bigint,

The data looks like this:

1000045,"GLENN M HINES","","C","N","N","317 BURNT BROW RD","HAMMOND","ME","04730","US","317 BURNT BROW RD","HAMMOND","ME","04730","US","(207) 532-4141","","","19-NOV-13","20000","2012","23-JAN-02","ME","1","2"
1000050,"ROGER L BUNCH","","C","N","N","108 ST CHARLES CT","GLASGOW","KY","42141","US","108 ST CHARLES CT","GLASGOW","KY","42141","US","(270) 651-3940","","","","72000","2001","23-JAN-02","KY","1","1"

I have tried doing this:

COPY CC FROM 'C:\Users\Owner\Documents\FMCSA Data\FMCSA_CENSUS1_2016Sep.txt' DELIMITER ',' CSV HEADER NULL '';

But I get this error:

ERROR: invalid input syntax for type date: "" CONTEXT: COPY cc, line 24, column mcs150_date: "" ********** Error **********

ERROR: invalid input syntax for type date: "" SQL state: 22007 Context: COPY cc, line 24, column mcs150_date: ""

This is probably pretty simple, but none of the solutions I've found online did not work.

Answer 1

You need to specify the QUOTE character so that "" would be interpreted as NULL, like so:

COPY CC FROM 'C:\Users\Owner\Documents\FMCSA Data\FMCSA_CENSUS1_2016Sep.txt' DELIMITER ',' CSV HEADER QUOTE '"' NULL '';

QUOTE '"' was the addition.

Docs: https://www.postgresql.org/docs/current/static/sql-copy.html