pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Copying null values
2+ messages / 2 participants
[nested] [flat]

* Copying null values
@ 2001-04-10 12:17  Amanda Riera <amanda@labtie.mmt.upc.es>
  0 siblings, 1 reply; 2+ messages in thread

From: Amanda Riera @ 2001-04-10 12:17 UTC (permalink / raw)
  To: 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















^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Copying null values
@ 2001-04-10 14:21  Christof Glaser <gcg@gl.aser.de>
  parent: Amanda Riera <amanda@labtie.mmt.upc.es>
  0 siblings, 0 replies; 2+ messages in thread

From: Christof Glaser @ 2001-04-10 14:21 UTC (permalink / raw)
  To: Amanda Riera <amanda@labtie.mmt.upc.es>; pgsql-sql

On Tuesday, 10. April 2001 14:17, Amanda Riera wrote:
> 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:
>
> COPY bill FROM '/home/amanda/informatica/provas/bills-copy.dat' USING
> DELIMITERS ',' WITH NULL AS '\,';

Just try 
  COPY ... WITH NULL AS ''; 

HTH,

Christof
-- 
          gl.aser . software engineering . internet service
       http://gl.aser.de/  . Planckstraße 7 . D-39104 Magdeburg
Tel. +49.391.7 44 77 10 . Fax +49.391.7 44 77 13 . Mobil 0177.77 92 84 3



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2001-04-10 14:21 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-04-10 12:17 Copying null values Amanda Riera <amanda@labtie.mmt.upc.es>
2001-04-10 14:21 ` Christof Glaser <gcg@gl.aser.de>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox