Thread: COPY FROM and NULL AS does not work

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 tried
COPY table (columns) FROM textfile
DELIMITER AS ';' NULL AS '' CSV;
and
COPY table (columns) FROM textfile
DELIMITER AS ';' NULL AS '' CSV QUOTE AS '"';
but both commands insert empty strings where the text file's
data is "".

mydb=# CREATE TABLE tmptable
mydb-# (
mydb(#   one character varying,
mydb(#   two character varying
mydb(# )
mydb-# WITHOUT OIDS;
CREATE TABLE
mydb=#
mydb=# COPY tmptable (one, two)
mydb-# FROM 'E:\\Production\\Statistiques-Web\\temp.csv'
mydb-# DELIMITER AS ';' NULL AS '' CSV QUOTE AS '"';
COPY
mydb=#
mydb=# SELECT * FROM tmptable WHERE one = '';
  one | two
-----+-----
      |
      |
(2 rows)

mydb=# SELECT * FROM tmptable WHERE one IS NULL;
  one | two
-----+-----
(0 rows)

Content of the temp.csv file is :
"";""
"";""

What am I doing wrong ?
Thanks !

--
Arnaud

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

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/

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