Alban Hertroys wrote:
> The problem the OP is pointing out seems difficult to solve. A
> sequence doesn't know about existing records with a possibly higher
> number than the sequence is at.
>
> This may be worked around by keeping a list of numbers used up
> beyond the current sequence value so the sequence knows what
> numbers to skip, but that has problems of its own (if there are
> many such numbers, or if the sequence gets created after data has
> been added to the list). It gets ugly.
I would just have a trigger that sets the serial to NEW.id + 1.
Dunno if this has concurrency issues, though, and it may leave huge
gaps in the key space, and (more importantly) use up your sequence
too quickly.
I have, in fact, had situations where I wanted a serial PK, =and= I
needed to insert with external IDs sometimes - essentially a mix of
natural and surrogate keys (shudder). It turned out that the natural
keys were always positive, so I set up the sequence to range
=downward= from 0.
- John D. Burger
MITRE