Re: COPY FROM and NULL AS does not work

Richard Huxton a écrit :
> 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).

Indeed ! I thought that the "NULL AS" parameter would
override this, but apparently not !

Thanks for this clarification (and thanks to Dimitri too) !
Since I have an INSERT trigger on this table, I can easily
handle the '' -> NULL there !

Regards
--
Arnaud