Re: COPY FROM and NULL AS does not work

Le vendredi 1 décembre 2006 11:28, Arnaud Lesauvage a écrit :
> 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>

As soon as you quote a string, COPY will consider it not to be NULL, but empty
string if there's no content.
You have to express NULL as ;; with your settings, as shown here:

pgloader=# create table tmptable(one varchar, two varchar);
CREATE TABLE
pgloader=# copy tmptable from stdin DELIMITER AS ';' NULL AS '' CSV QUOTE
AS '"';
Entrez les données à copier suivies d'un saut de ligne.
Terminez avec un antislash et un point seuls sur une ligne.
>> ;""
>> ;""
>> \.
pgloader=# select * from tmptable where one is null;
 one | two
-----+-----
     |
     |
(2 lignes)

Regards,
--
Dimitri Fontaine
http://www.dalibo.com/