Re: COPY FROM and NULL AS does not work

From: Arnaud Lesauvage <thewild(at)freesurf(dot)fr>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: COPY FROM and NULL AS does not work
Date: 2006-12-01 13:12:44
Message-ID: 45702A4C.2090802@freesurf.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message George Weaver 2006-12-01 13:17:45 Re: PostgreSQL doesn't accept connections when Windows
Previous Message Niklas Johansson 2006-12-01 12:31:07 Re: Separation of clients' data within a database