Re: Sequence Roll Over

Mat <psql-mail@freeuk.com> writes:
> What happens when a sequence for auto-numbered records gets to its
> maximum value and another record is added?

You can set the sequence to wrap around, or to report errors on
subsequent nextval attempts; I believe the latter is the default
behavior.

> Does using a sequence for this limit the amount of records the table can
> hold?

Surely you can work out the implications for yourself.  But I find it
hard to believe that this is a practical concern if you're using bigint
serial fields.

> If records are deleted, is there a standard way of renumbering all
> records so their ID's are consecutive starting from zero and then
> reseting the sequence to the highest ID value?

You could probably gin up something involving resetting the sequence
and then doing
    UPDATE tab SET col = nextval('seq');
However, I can't imagine that this is a good idea.  What's the point of
assigning a made-up primary key if you're going to change it later?
Everything I've read on database design says that you don't change
a record's primary key, ever.

            regards, tom lane