Re: COPY FROM and NULL AS does not work

Arnaud Lesauvage wrote:
> HI List,
>
> Trying to import data from a text file, with a semicolon as the
> delimiter, double-quotes as the quoting character.
>
> I would like empty strings to be inserted as NULL values in a varchar
> column. In the text file, they are writen as :
> <some columns>;"";<some columns>

I'm not sure you can do that. From the manuals:
"The CSV format has no standard way to distinguish a NULL value from an
empty string. PostgreSQL's COPY handles this by quoting. A NULL is
output as the NULL string and is not quoted, while a data value matching
the NULL string is quoted. Therefore, using the default settings, a NULL
is written as an unquoted empty string, while an empty string is written
with double quotes (""). Reading values follows similar rules. You can
use FORCE NOT NULL to prevent NULL input comparisons for specific columns."

Looks like you'll have to run a separate UPDATE query after the import
(or pre-process your input file).

--
Richard Huxton
Archonet Ltd