Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I love that SERIALs can wrap around with CYCLE.
However, I can't seem to figure out if that there are lingering values, will Postgres skip over them or throw an error.
In other words, I have a table that needs UNIQUE auto incremented values. Many will be destroyed over time, but many will remain. After the max value has been reached, does Postgres increment past it, or will it throw an error because the next incremented value already exists?
The sequence will return values in order, whether or not they exist in the table. Your code will need to defend against that. Any method to defend against possibly already-in-use serial values will really hurt concurrency - and if your DB is big enough that you're wrapping int4 serial columns, you care about concurrency.
Most people just use bigint keys and don't worry about ID re-use. You won't run out.
