PostgreSQL not importing NULL values from a CSV file

Question

I created a huge database in PostgreSQL where I have integer fields that can take NULL values. However when I am trying to import data into the database from a CSV file, it throws me the following error:

ERROR:  invalid input syntax for integer: "NULL"
CONTEXT:  COPY reports_results, line 1, column teacher_id: "NULL"

I am trying to import into the database using the copy command like:

copy  reports_results(test_type_id, teacher_id)
from '/Downloads/mydata.csv';

So what I understood so far is that the database will not accept NULL values in quotes ie. "NULL". But my csv file does not have any quotes wherever the NULL values are present. How do I import them in such cases?

Answer 1

Assuming your csv is something like:

1,NULL
2,NULL

Your command would be:

copy  reports_results(test_type_id, teacher_id)
from '/Downloads/mydata.csv';
with NULL as 'NULL'