Re: Sequence Roll Over

Mat <psql-mail(at)freeuk(dot)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