Take the 2-minute tour ×
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's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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.

share|improve this answer
1  
+1 for bigint suggestion –  dezso Feb 10 at 9:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.