Copy NULL values present in csv file to postgres

Question

I have csv file (y.csv) in the folowing format:

 's', '1999-10-10', '1999-12-12'
 'b', '99-10-10 BC', '1-10-10 BC'
 'c', 'NULL', 'NULL'

I have a few NULL values (for date) in it which I have indicated through the string 'NULL'.

I am trying to copy the csv file into postgres. For doing so I have created a table:

create table r (id character varying(255), timebegin date, timeend date);

Now I am trying to copy the above .csv file into postgres using the command

copy r from '/home/y.csv' delimiter ',' csv;
ERROR:  invalid input syntax for type date: " 'NULL'"
CONTEXT:  COPY r, line 1, column timebegin: " 'NULL'"

On doing so I am getting an error with NULL. Can someone please help me figure out the error and correct it.

Answer 1

Have you tried it?

 copy r from '/home/y.csv' delimiter ',' csv WITH NULL AS 'null';

Answer 2

after giving the path of the file, we need to use 'With' before adding other paramters -

copy r from '/home/y.csv' with delimiter ',' NULL AS 'null' csv header;

Answer 3

The error is because you are inserting NULL as string. Remove the quotes around NULL and it should work.

Answer 4

1st Replace all NULL with ' ' (Space).

Then COPY r FROM '/home/y.csv' WITH DELIMITER ',' NULL as ' ' CSV;