Re: Better alternative for Primary Key then serial??

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