Postgres Pro
Downloads
Home   >   mailing lists

Thread: Quoted NULLs with COPY FROM

Quoted NULLs with COPY FROM

From
"George Pavlov"
Date:
I need to load CSV files that have quotes in data fields that I want to
map to NULLs in the destination table. So if I see ...,"",... that needs
to be mapped to a NULL (in an INTEGER field in this particular case).
Are there any COPY command options that can do that? It seems that PgSQL
COPY expects the NULL to be always unquoted. There is an option (FORCE
NOT NULL) for doing the opposite. How do I specify that the NULLs are
quoted? I am on 8.0.5.

George

Re: Quoted NULLs with COPY FROM

From
Bruce Momjian
Date:
George Pavlov wrote:
> I need to load CSV files that have quotes in data fields that I want to
> map to NULLs in the destination table. So if I see ...,"",... that needs
> to be mapped to a NULL (in an INTEGER field in this particular case).
> Are there any COPY command options that can do that? It seems that PgSQL
> COPY expects the NULL to be always unquoted. There is an option (FORCE
> NOT NULL) for doing the opposite. How do I specify that the NULLs are
> quoted? I am on 8.0.5.

Did you try NULL AS ''?

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: Quoted NULLs with COPY FROM

From
"George Pavlov"
Date:
> > I need to load CSV files that have quotes in data fields
> > that I want to
> > map to NULLs in the destination table. So if I see
> > ...,"",... that needs
> > to be mapped to a NULL (in an INTEGER field in this
> > particular case).
> > Are there any COPY command options that can do that? It
> > seems that PgSQL
> > COPY expects the NULL to be always unquoted. There is an
> > option (FORCE
> > NOT NULL) for doing the opposite. How do I specify that the
> > NULLs are quoted? I am on 8.0.5.
>
> Did you try NULL AS ''?

yes i did. that is the default and does not change the outcome (same
errors about trying to insert an empty string into a numeric field.

Re: Quoted NULLs with COPY FROM

From
Richard Huxton
Date:
George Pavlov wrote:
>> Did you try NULL AS ''?
>
> yes i did. that is the default and does not change the outcome (same
> errors about trying to insert an empty string into a numeric field.

Well there's your problem. Quotes tend to imply a text field. Assuming
you don't want to write a short Perl script to pre-process the file the
simplest way would be to import into a temporary table with text columns
rather than numeric, then copy from that into the real table (with CASE
or two queries).

--
   Richard Huxton
   Archonet Ltd