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@xxxxxxxxxxxxx> Sent: Thursday, January 23, 2025 11:42 AM To: Campbell, Lance <lance@xxxxxxxxxxxx> Cc: pgsql-admin@xxxxxxxxxxxxxx Subject: Re: Sequence Cycle question "Campbell, Lance" <lance@xxxxxxxxxxxx> 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
