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.

sqlint · Accepted Answer · 2013-09-26 17:45:44Z

62

Have you tried it?

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

answered Sep 26, 2013 at 17:45

sqlint

1,1199 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

monteirobrena Over a year ago

I try but I receive this error: ERROR: syntax error at or near "WITH NULL";

Alexander Bird Over a year ago

copy r from '/home/y.csv' with delimiter as ',' NULL AS 'null' csv; I don't know if syntax changed in later version of pgsql, but that's what worked for me.

Michael Silver Over a year ago

@monteirobrena, leave off the 'WITH'. It will work without it.

Marco Over a year ago

I usually go with copy table from filename WITH (FORMAT CSV, NULL "NULL") and it works

Pct Mtnxt Over a year ago

For me, the syntax that I use, working on PG 9.3 and above, is copy r from '/home/y.csv' WITH (delimiter ',', FORMAT csv, NULL 'null)';

|

Hartley Brody · Accepted Answer · 2023-01-23 19:25:15Z

10

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;

edited Jan 23, 2023 at 19:25

Hartley Brody

9,23714 gold badges40 silver badges49 bronze badges

answered Apr 14, 2020 at 0:02

Nidhi Mahajan

1091 silver badge3 bronze badges

Comments

heretolearn · Accepted Answer · 2013-09-26 17:50:23Z

3

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

answered Sep 26, 2013 at 17:50

heretolearn

6,5654 gold badges32 silver badges55 bronze badges

2 Comments

Kermit Over a year ago

This is not a solution. What if this was an automated process?

heretolearn Over a year ago

@FreshPrinceOfSO : I know this may not be an ideal solution but as it was mentioned in the question as I have a few NULL values (for date) in it which I have indicated through the string 'NULL'. It is clear that the error was because of trying to insert a string in the date column.

Atanu · Accepted Answer · 2020-07-17 12:50:49Z

1

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

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

answered Jul 17, 2020 at 12:50

Atanu

191 bronze badge

Collectives™ on Stack Overflow

Copy NULL values present in csv file to postgres

4 Answers 4

6 Comments

Comments

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

Comments

2 Comments

Comments

Linked

Related