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.