Re: PGSQL 9.3.2 COPY command issues

spake@surewest.net wrote:
> Hi there. My first post to this list. Hopefully, as time goes by, I will be able to answer more
> questions than I ask.
> 
> I'm trying to import a very large CSV file of npi data into psql 9.3.2 using the COPY command. The
> data in the CSV file is surrounded by quotes. Although there is a header, the data looks like this:
> "1679576722","1","","","","WIEBE",  etc.
> 
> Here is my psql command:
> COPY data FROM '/Developer/data_20140608.csv' DELIMITER ',' CSV  HEADER;
> 
> and here is the error I get:
> ERROR:  invalid input syntax for type numeric: ""
> CONTEXT:  COPY npi_data, line 2, column replacement_npi: ""
> 
> psql seems to be choking on the empty fields that are quoted. Any suggestions as to how I get around
> this? I've tried including the QUOTES statement but it made no difference.

The problem is that the empty string is not a valid integer value.
It might be tempting to tell COPY that it should consider that as a NULL value:

COPY tabname FROM 'filename' (FORMAT 'csv', NULL '""');

but that results in

ERROR:  CSV quote character must not appear in the NULL specification

So it looks like you'll have to preprocess these files, e.g. with

sed -e 's/""/(null)/g'

and then you could import with

COPY tabname FROM 'filename' (FORMAT 'csv', NULL '(null)');

Yours,
Laurenz Albe