Alter sequence restart with selected value...

[Jeff Ross <jross@xxxxxxxxxx>]

I'm using copy to insert a bunch of rows into a new table with a unique
primary key.  Copy is correctly incrementing the primary key, but
apparently the sequence itself is never updated because when I go to
insert again I get a constraint violation.

Here's the start of the new table creation:

CREATE TABLE training_programs (
    trg_prg_id integer NOT NULL primary key DEFAULT
nextval('training_programs_trg_prg_id_seq'),

I then use copy (select about half the columns in the original table) to 
'/tmp/training_programs.txt'

and then

copy training_programs from '/tmp/training_programs.txt'

After this I get the following:

wykids=# select max(trg_prg_id) from training_programs;
 max
------
 4893

wykids=# select nextval('training_programs_trg_prg_id_seq');
 nextval
---------
       1
(1 row)

Since I'm doing this against a copy of a live database in preparation
for running it against the real thing, I never know how many records
will be in training_programs.

I'm trying, then, to do something like this:

alter sequence training_programs_trg_prg_id_seq restart with (select
(max(trg_prg_id) + 1) from training_programs);

but that isn't working.

Thanks in advance for any help!

Jeff Ross


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

              http://archives.postgresql.org/