Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> You won't get that. serial (and sequences) are guaranteed to give numbers
> that haven't shown up in the sequence (note: you can still get duplicates
> if you set values yourself, you can get around this with triggers -
The SERIAL type implicitly adds a UNIQUE index, so you don't need to
worry about uniqueness even if you sometimes manually insert values.
Stephan's correct that sequences do not guarantee consecutive numbers,
only distinct numbers (because numbers obtained by transactions that
later fail won't get recycled). We consider that we have good and
sufficient reasons for doing it that way.
Cindy also mentioned a requirement that the numbers never be <= 0.
While the sequence won't generate such values (at least not with default
parameters), there wouldn't be anything stopping a manual insertion of
a value <= 0. If it's really important to prevent that, she could add
something like CHECK (id > 0) to the column definition.
regards, tom lane