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.