Re: COPY INTO and the SERIAL data type

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.