Copying null values
От | Amanda Riera |
---|---|
Тема | Copying null values |
Дата | |
Msg-id | 3AD2F9DD.D1A91274@labtie.mmt.upc.es обсуждение исходный текст |
Ответы |
Re: Copying null values
|
Список | 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 NOTNULL,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 1bill_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
В списке pgsql-sql по дате отправления: