Search Postgresql Archives

Re: Copy From csv file with double quotes as null

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi All,

I am apparently totally misreading how to import data using the COPY FROM command, can someone give assistance ?

I have two issues, both dealing with double quotes as NULL.  The data is CSV with NULL being represented by a double quote (e.g. "") in all columns of the table.

ISSUE A)  The following command bombs:

COPY testdata FROM 'c:/temp/test.csv' CSV HEADER;

with the following error:

ERROR:  invalid input syntax for type double precision: ""
CONTEXT:  COPY testdata, line 7, column latitude: ""

********** Error **********

ERROR: invalid input syntax for type double precision: ""
SQL state: 22P02
Context: COPY testdata, line 7, column latitude: ""

So, latitude is a double precision column and  I think that PostgreSQL is interpreting the double quote as a NULL string and then it can not be placed into that column because it is a double precision column.

Issue B)  I have an associated issue with a text value where the NULL in the data being represented by a double quote (e.g. "") is being inputed as a quote.  I can not use the switch NULL AS '"' because PostgreSQL says "the quote character must not appear in the NULL specification"


Given the file sizes are huge, I would rather not have to try to preprocess the data.  Is there anyway the COPY FROM command can handle this data smoothly ?
 
-- 
-Don 

Don Catanzaro, PhD                  
Landscape Ecologist
dgcatanzaro@xxxxxxxxx
16144 Sigmond Lane
Lowell, AR 72745
479-751-3616

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux