Postgres COPY TO NULL integers

问题:

I have a CSV that contains various columns. One of the columns contains integer data. However, when running a copy to:

COPY soc 
FROM '~/soc.asc'
WITH DELIMITER '$';

I'm getting the following:

ERROR:  invalid input syntax for integer: ""
CONTEXT:  COPY soc, line 1, column soc_code: ""

as it appears that Postgres is trying to stick an empty string in an integer. How can I fix this? I don't really want to fudge my schema to suit the import if it can be helped.

回答1:

You can tell postgres to interpret some value as NULL, for example:

COPY soc FROM '~/soc.asc' WITH DELIMITER AS '$' NULL AS ''