Thread: serial sequence problem with existing keys

I've had to modify a parent table by recreating it (I substituted integer
foreign key values from lookup tables for text in 2 fields. As postgres
doesn't have a DROP COLUMN command I couldn't revise the table structure
without rebuilding it) .

It has a serial data type for the primary key. I'm importing the data back
in and need to keep the original primary key values, as they're used in
several child tables.

But, if I put in my own key values in a serial field the auto-increment
sequence uses the next number from where it left off, and not the max value,
and complains that it can't create a duplicate key.

Is there any way around this, or am I going to have to programmatically
increment the key and change serial to int?


--
Randy Perry
sysTame
Mac Consulting/Sales

phn                 561.589.6449
mobile email        help@systame.com

On Wed, 31 Oct 2001, Randall Perry wrote:

> I've had to modify a parent table by recreating it (I substituted integer
> foreign key values from lookup tables for text in 2 fields. As postgres
> doesn't have a DROP COLUMN command I couldn't revise the table structure
> without rebuilding it) .
>
> It has a serial data type for the primary key. I'm importing the data back
> in and need to keep the original primary key values, as they're used in
> several child tables.
>
> But, if I put in my own key values in a serial field the auto-increment
> sequence uses the next number from where it left off, and not the max value,
> and complains that it can't create a duplicate key.
>
> Is there any way around this, or am I going to have to programmatically
> increment the key and change serial to int?

Check out setval('<sequence name>', <number>) to change the sequence's
current value.

>
> Check out setval('<sequence name>', <number>) to change the sequence's
> current value.
>
>
>
That done did it.

Thanks

--
Randy Perry
sysTame
Mac Consulting/Sales

phn                 561.589.6449
mobile email        help@systame.com