From: | "John D(dot) Burger" <john(at)mitre(dot)org> |
---|---|
To: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Better alternative for Primary Key then serial?? |
Date: | 2007-12-13 13:12:56 |
Message-ID: | 8C2116E9-DA9F-4CAC-9CF1-016E59ABE2ED@mitre.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2007-12-13 13:18:04 | Re: Slow PITR restore |
Previous Message | Trinath Somanchi | 2007-12-13 13:02:44 | Re: SQL Query |