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
Best Answer
I get these issues all the time. PostgreSQL has made a really massive and stupid mistake from a UI perspective by having two modes
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...
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.
That seems to do what you want. You should be able to read it with the same options.