Re: Sequence Cycle question

"Campbell, Lance" <lance(at)illinois(dot)edu> writes:
> Table X has records that have been removed over time randomly. There are IDs that cover a wide range of values between 1 and 1,000,000.
> When the primary key ID, which is a sequence, reaches 1,000,000 then the next sequence value will start back at 1.
> What would happen if I had a primary key for ID of 5 still in use? When I reach 5 will the sequence skip that number and go to 6 instead?

No, the sequence has no idea about what is in the table. It will
generate "5" when it's time to, and then your insert will get a
duplicate-key violation.

You could work around that by retrying the insert, but it might
be better to reconsider whether you want a cycling sequence
for this application.

regards, tom lane