From: | Marc SCHAEFER <schaefer(at)alphanet(dot)ch> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: COPY INTO and the SERIAL data type |
Date: | 2001-05-10 09:35:07 |
Message-ID: | Pine.LNX.3.96.1010510113036.1364B-100000@defian.alphanet.ch |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Thu, 10 May 2001, Jonathan Sand wrote:
> I want to use the COPY command to read a bunch of data files. These
> files don't contain an id, so I want to use the SERIAL data type to
> auto-number the generated rows. COPY complains.
Destination table:
CREATE TABLE destination (id SERIAL, truc INT, temps DATE);
Temporary table:
CREATE TABLE temp1 (truc INT, temps DATE);
Please do:
COPY temp1 FROM STDIN;
INSERT INTO destination
SELECT nextval('destination_id_seq'), *
FROM temp1;
DROP TABLE temp1;
with data:
34 2001-03-05
52 2001-02-01
\.
and you will get:
SELECT * FROM destination;
id|truc| temps
--+----+----------
1| 34|2001-03-05
2| 52|2001-02-01
(2 rows)
NOTE: this doesn't number the data in sequence, but in the order the
SELECT returns them. If you need an absolute order, use the following Perl
script instead:
my $count = 1;
while (<>) {
print $count++, "\t", $_;
}
and insert directly in the database.
From | Date | Subject | |
---|---|---|---|
Next Message | martin.chantler | 2001-05-10 09:38:23 | Re: ER diagrams |
Previous Message | Gilles DAROLD | 2001-05-10 08:17:36 | Re: Oracle to Pg tool |