Copy from file: ERROR: invalid input syntax for integer: "\N"

Question

My table column defined intcol integer; no NOT NULL constraint. In the load file most values are integers, but a few are null values labeled with the '\N' default as suggested by the copy manual. Can the copy command convert '\N' to null values in the table? I also tried with null 'NA'. Got similar error message.

ERROR:  invalid input syntax for integer: "\N"
CONTEXT:  COPY table_name, line 111, column position3: "\N"

Any trick?

Answer 1

Use the NULL parameter. From the documentation:

NULL

Specifies the string that represents a null value. The default is \N (backslash-N) in text format, and an unquoted empty string in CSV format.

Example

copy my_table from '/my/csv_file.csv' (format csv, null '\N');