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

Question

I have a CSV file from which I am trying to use the Postgres COPY to populate a table from this CSV file. One of the columns of the NEXT_VISIT table has a data_type date. Some of the corresponding fields in the CSV file that must be included in this date column have null values.

The Copy command is executed:

 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 an 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 do I run a copy command and force Postgres to accept that some of the fields in the CSV file corresponding to NEXT_VISIT have a value of "" ?

Answer 1

I had the same problem and for me it decided to use the WITH NULL '' statement. WITH NULL '' It is important not to have space between the apostrophes.

I originally used the WITH NULL ' ' operator WITH NULL ' ' and received the same error message that you made (ERROR: syntax error in or next to "WITH NULL").

But when I removed 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 '' 

Read more here: postgresql COPY