Received: from dukas.upc.es (dukas.upc.es [147.83.2.62]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f3ACEp817775 for ; Tue, 10 Apr 2001 08:14:52 -0400 (EDT) (envelope-from amanda@labtie.mmt.upc.es) Received: from selene.upc.es (selene.upc.es [147.83.83.65]) by dukas.upc.es (8.10.1/8.10.1) with ESMTP id f3ACARS18729 for ; Tue, 10 Apr 2001 14:10:28 +0200 (MET DST) Received: from batch.upc.es (root@batch.upc.es [147.83.92.19]) by selene.upc.es (8.8.8/8.8.6) with ESMTP id OAA19120 for ; Tue, 10 Apr 2001 14:14:45 +0200 (MET DST) Received: from labtie.mmt.upc.es (amanda@tester2.upc.es [147.83.92.52]) by batch.upc.es (8.9.3/8.9.3/Debian/GNU) with ESMTP id OAA10885 for ; Tue, 10 Apr 2001 14:14:45 +0200 Message-ID: <3AD2F9DD.D1A91274@labtie.mmt.upc.es> Date: Tue, 10 Apr 2001 14:17:33 +0200 From: Amanda Riera Organization: CTTC X-Mailer: Mozilla 4.76 [en] (X11; U; Linux 2.2.17 i686) X-Accept-Language: en MIME-Version: 1.0 To: pgsql Subject: Copying null values Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200104/133 X-Sequence-Number: 2480 Hi all, I am using: Debian 2.2 (kernel 2.2.18) PgSQL version 7.0.3 I would like empty fields from a file being recognised as NULL values. It doesn't seem to work when a COPY is done, but when I make an INSERT without specifying the value, this is recognised as a NULL The file I want to copy is: ,0,Unknown,01/01/3000,01/01/3000,01/01/3000,0, 1,3,,01/01/3000,01/01/3000,01/01/3000,0, 2,,Unknown,01/01/3000,01/01/3000,01/01/3000,0, What I am doing is: -------------------------------------------------------------------------- CREATE TABLE bill( bill_id SERIAL PRIMARY KEY, code INTEGER, bill_number CHAR(20), issue_date DATE DEFAULT CURRENT_DATE NOT NULL, expire_date DATE, negotiate_date DATE, amount DECIMAL(9,2) NOT NULL, comment TEXT ); CREATE FUNCTION bill_id_max() RETURNS INT AS 'SELECT max(bill_id) FROM bill' LANGUAGE 'sql'; COPY bill FROM '/home/amanda/informatica/provas/bills-copy.dat' USING DELIMITERS ',' WITH NULL AS '\,'; SELECT setval('bill_bill_id_seq',bill_id_max()); INSERT INTO bill (code,amount) VALUES (34,100000); INSERT INTO bill (bill_number,amount) VALUES ('494949/949',100000); SELECT * FROM bill WHERE bill_number IS NULL; SELECT * FROM bill WHERE code IS NULL; COPY bill TO '/tmp/copytest.out' USING DELIMITERS ','; ---------------------------------------------------------------------------- The result is: setval -------- 2 (1 row) INSERT 29322 1 INSERT 29323 1 bill_id | code | bill_number | issue_date | expire_date | negotiate_date | amount | comment ---------+------+-------------+------------+-------------+----------------+-----------+--------- 3 | 34 | | 2001-04-10 | | | 100000.00 | (1 row) bill_id | code | bill_number | issue_date | expire_date | negotiate_date | amount | comment ---------+------+----------------------+------------+-------------+----------------+-----------+--------- 4 | | 494949/949 | 2001-04-10 | | | 100000.00 | (1 row) COPY This is the result file /tmp/copytest.out 0,0,Unknown ,3000-01-01,3000-01-01,3000-01-01,0.00, 1,3, ,3000-01-01,3000-01-01,3000-01-01,0.00, 2,0,Unknown ,3000-01-01,3000-01-01,3000-01-01,0.00, 3,34,\N,2001-04-10,\N,\N,100000.00,\N 4,\N,494949/949 ,2001-04-10,\N,\N,100000.00,\N Thanks