COPY FROM and NULL AS does not work

Поиск
Список
Период
Сортировка
От Arnaud Lesauvage
Тема COPY FROM and NULL AS does not work
Дата
Msg-id 457003C7.3050708@freesurf.fr
обсуждение исходный текст
Ответы Re: COPY FROM and NULL AS does not work  (Richard Huxton <dev@archonet.com>)
Re: COPY FROM and NULL AS does not work  (Dimitri Fontaine <dim@dalibo.com>)
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: "Willy-Bas Loos"
Дата:
Сообщение: Re: named cache
Следующее
От: Richard Huxton
Дата:
Сообщение: Re: Restoring database from files on disk