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","[phone]","","","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","[phone]","","","","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

Answer 2

I ended up importing as text and then altering the tables according to the correct type.

Answer 3

Just for any future reference.

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

says,

NULL

Specifies the string that represents a null value. The default is \N (backslash-N) in text format, and an unquoted empty string in CSV format. You might prefer an empty string even in text format for cases where you don't want to distinguish nulls from empty strings. This option is not allowed when using binary format.

so remove the quote on the empty string to obtain an NULL value on these empty date values.

Answer 4

Just for future reference, the issue here was probably the date format of the not-null date values. It's common for an MS Excel file saved to CSV to have that format, 01-JUL-16, but PostgreSQL will not know what to do with it unless you've first converted it to one of the standard date formats[1]. But PostgreSQL won't be able to accept that format "out of the box" when doing a COPY, because it'll be presented with a date string that doesn't match one of the format masks that it can handle by default.

That, AND the null handling for null date values.

[1] (and perhaps dealt with the consequences of having a 2-digit year upfront, particularly that years prior to 1969 will be interpreted as 20xx).