Copying null values

From: Amanda Riera <amanda(at)labtie(dot)mmt(dot)upc(dot)es>
To: pgsql <pgsql-sql(at)postgresql(dot)org>
Subject: Copying null values
Date: 2001-04-10 12:17:33
Message-ID: 3AD2F9DD.D1A91274@labtie.mmt.upc.es
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Thomas Good 2001-04-10 12:26:35 The Momjian Tome / DBA Certification
Previous Message Peter Peltonen 2001-04-10 11:07:50 Re: 7.1 out soon?