Error on using copy Command in Postgres (ERROR: invalid input syntax for type date: "")

Question

I have a CSV file from which I am trying to use Postgres COPY command in order to populate a table from that CSV file. One of the table columns NEXT_VISIT is of a date data_type. Some of the corresponding fields in the CSV file which are supposed to go into this date column have null values.

The Copy command am running is like so:

COPY "VISIT_STAGING_TABLE" from E'C:\\Users\\Sir Codealot\\Desktop\\rufijihdss-2007-2010\\rufijihdss\\VISIT_TEST.CSV' CSV HEADER 

When I run this command I get the error:

ERROR:  invalid input syntax for type date: ""
CONTEXT:  COPY VISIT_STAGING_TABLE, line 2, column NEXT_VISIT: ""

********** Error **********
ERROR: invalid input syntax for type date: ""
SQL state: 22007
Context: COPY VISIT_STAGING_TABLE, line 2, column NEXT_VISIT: ""

How can I run the copy command and get Postgres to accept that some of the fields in the CSV file corresponding to NEXT_VISIT have values ""?

Answer 1

I was having the exact same problem, and what solved it for me was to use the statement WITH NULL ''. It is important not to have a space between the apostrophes.

I originally used the statement WITH NULL ' ' and got the same error message you did (ERROR: syntax error at or near "WITH NULL").

But when I eliminated the space between the apostrophes it worked.

Answer 2

Add WITH NULL AS '' to your command (COPY expects NULLs to be represented as "\N" (backslash-N) by default).

COPY "VISIT_STAGING_TABLE" from E'C:\\Users\\Sir Codealot\\Desktop\\rufijihdss-2007-2010\\rufijihdss\\VISIT_TEST.CSV' WITH CSV HEADER NULL AS ''

More details here: postgresql COPY