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?

Improve this question

1 Answer 1

Reset to default
6

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
2
  • 2
    Initially I thought it (\N) is the default, and you don't have to specify it.
    – Kemin Zhou
    Oct 24, 2017 at 16:35
  • 2
    @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, 2017 at 17:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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