Failed import of date using COPY

Question

I'm trying to import a CSV file into a table in Postgres, and it's bombing out on the first date field. The dates are in the format dd/mm/yyyy and there isn't a date in the field in question in the CSV.

As such I'm using the following:

set datestyle = 'ISO,DMY'; 

\copy companies_house_snapshot 
FROM 'BasicCompanyDataAsOneFile-2017-04-01.csv' 
WITH DELIMITER ',' CSV HEADER NULL AS '';

Which I thought should be sufficient, the error message is:

ERROR:  invalid input syntax for type date: ""  
CONTEXT:  COPY companies_house_snapshot, line 2, column dissolution_date: ""

Where dissolution_date is nullable and in the CSV as ""

I'm using PostgreSQL 9.5.6 on Ubuntu 16.04

Where am I going wrong?

If I use '""' as NULL, I get:

ERROR:  CSV quote character must not appear in the NULL specification

Answer 1

If I use '""' as NULL, I get:

I get these issues all the time. PostgreSQL has made a really massive and stupid mistake from a UI perspective by having two modes

1. CSV
2. NON-CSV

CSV according to them is RFC4180. However, it's not all that useful to adhere to a spec if no one else does and RFC4180 is probably the least used spec on the internet. Start using it people.

Anyway to solve these problems the easy method is to create a test table...

CREATE TABLE foo ( x int, y int, z int );
INSERT INTO foo (x,y,z) VALUES (1,null,2);

And then to get to work brute forcing options for both modes. That's why I do anyway because I find it so unintuitive. It's pretty quick though.

COPY foo TO STDOUT NULL AS '""' DELIMITER ',';

That seems to do what you want. You should be able to read it with the same options.

COPY foo FROM '/tmp/test.csv' NULL AS '""' DELIMITER ',';