COPY FROM and NULL AS does not work

From: Arnaud Lesauvage <thewild(at)freesurf(dot)fr>
To: pgsql-general(at)postgresql(dot)org
Subject: COPY FROM and NULL AS does not work
Date: 2006-12-01 10:28:23
Message-ID: 457003C7.3050708@freesurf.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Teodor Sigaev 2006-12-01 10:32:04 Re: Tsearch2 - Error using tsquery object in GROUP BBY
Previous Message Willy-Bas Loos 2006-12-01 10:27:25 Re: named cache