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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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 for bigint suggestion – dezso Feb 10 '14 at 9:21

Your Answer


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