CODEWITHSUNDEEP
postgresqlcsv
Jannat Arora
Jannat Arora

Reputation: 2989

Copy NULL values present in csv file to postgres

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.

Upvotes: 36

Views: 66845

Answers (4)

Nidhi Mahajan
Nidhi Mahajan

Reputation: 99

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;

Upvotes: 9

Atanu
Atanu

Reputation: 19

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

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

Upvotes: 1

heretolearn
heretolearn

Reputation: 6545

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

Upvotes: 3

sqlint
sqlint

Reputation: 1089

Have you tried it?

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

Upvotes: 59

Related Questions