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.
