Обсуждение: Alter sequence restart with selected value...

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

On 9/18/07, Jeff Ross <jross@wykids.org> wrote:
> 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.

Try using setval.

select setval('seqname',select max(id) from tablename));

Scott Marlowe wrote:
> On 9/18/07, Jeff Ross <jross@wykids.org> wrote:
>> 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.
>
> Try using setval.
>
> select setval('seqname',select max(id) from tablename));
>
>
> !DSPAM:46eff995184363531088756!
>

Oh, I knew it was going to be something simple!

Than you, Scott.