Re: Migration problem - serial fields

On Mon, 4 Mar 2002, Rick Anderson wrote:

> The snag I'm encountering is with Identity fields in SQL7. I discovered the
> equivalent in PG -- sequences/serial field. I would like to use serial if
> possible, but here's the problem: getting the existing records over while
> *keeping the existing values* for the Identity/sequence ID fields. They
> actually transfer over fine, but the next insert into the PG table
> generates a duplicate ID error. It seems obvious that my transfer did not
> update the sequence used by the serial field. However, I'm just not sure of
> the best course of action.

Yes, the sequence is only updated if a value is not given (it's
effectively just a default so nextval doesn't get called otherwise)

> I've thought about the following as solutions:
>
> 1. Do the transfer, put values into the serial field, then find the highest
> value and manually set the sequence somehow to start from highest+1 (seed
> value). I don't know if this means moving away from serial field to a

This is probably the easiest. setval('<seq name>', highest value) should
be okay. IIRC this is what pg_dump does.

> "nextval of sequence" approach. I understand there is a problem with orphan
> sequences with serial fields if you drop tables, so maybe I shouldn't use
> serials anyway.

It's just a thing to remember when you drop the table. You run into
similar problems with standalone sequences anyway.