From: | "Donald Catanzaro, PhD" <dgcatanzaro(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Copy From csv file with double quotes as null |
Date: | 2010-09-08 18:48:25 |
Message-ID: | 4C87DA79.3030105@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)gmail(dot)com
16144 Sigmond Lane
Lowell, AR 72745
479-751-3616
From | Date | Subject | |
---|---|---|---|
Next Message | Sam Nelson | 2010-09-08 18:56:51 | Memory Errors |
Previous Message | Brar Piening | 2010-09-08 18:41:30 | Re: Postgres 32bit on Windows 64bit, related components |