1

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
  • Remove CSV and NULL AS '""' is possible ; but HEADER is not possible without CSV – slardiere Apr 5 '17 at 14:33
  • Sorry, I don't understand that – SWa Apr 5 '17 at 16:34
  • You can't use NULL AS '""' with the CSV option, but you can without it. But you use HEADER which could be use only with CSV. You have to choose between NULL AS '""' and HEADER. You can preprocess your file to remove the first line, by example. – slardiere Apr 6 '17 at 7:16
2

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 ',';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.