4

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?

5

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');
| improve this answer | |
  • Initially I thought it (\N) is the default, and you don't have to specify it. – Kemin Zhou Oct 24 '17 at 16:35
  • 1
    @KeminZhou \N is the default if you are using the default format. But once you switch the format to csv, the default is an unquoted empty string – jjanes Oct 24 '17 at 17:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.