RE: Sequence Cycle question

Lets say I was to set the cycle=true.  Once the IDs start back at 1, lets say we get to an ID of 5 where there is a
duplicate.  

Is there a trigger or something else, that I could associate with the table that would "catch/detect" the insert error.
If an error occurs it would then do a "fresh" insert with no specified ID so the sequence would naturally be
incremented?

I hope that made sense.


Thanks,

-----Original Message-----
From: Tom Lane <tgl@sss.pgh.pa.us>
Sent: Thursday, January 23, 2025 11:42 AM
To: Campbell, Lance <lance@illinois.edu>
Cc: pgsql-admin@postgresql.org
Subject: Re: Sequence Cycle question

"Campbell, Lance" <lance@illinois.edu> writes:
> Table X has records that have been removed over time randomly.  There are IDs that cover a wide range of values
between1 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
andgo 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
willget 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
sequencefor this application. 

            regards, tom lane