Quoted NULLs with COPY FROM

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

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.

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