On Tue, Aug 16, 2005 at 21:01:41 -0400,
Leif K-Brooks <eurleif@gmail.com> wrote:
> I want a column which is like a SERIAL, except that values can be
> changed after insertion without anything breaking. In other words, I
> don't want an error like this one to occur:
>
> database=> CREATE TABLE foo (bar SERIAL UNIQUE);
> CREATE TABLE
> database=> INSERT INTO foo(bar) VALUES(DEFAULT);
> INSERT 25410 1
> database=> UPDATE foo SET bar=2 WHERE bar=1;
> UPDATE 1
> database=> INSERT INTO foo(bar) VALUES(DEFAULT);
> ERROR: duplicate key violates unique constraint "foo_bar_key"
>
> I've tried defining bar as (SELECT MAX(bar) + 1 FROM foo), but that
> breaks concurrency. Is there a solution to this problem that works?
If people can insert or change values to anything at all, I don't as
how you have any choice but use a query to find a value not present
in the database and use that. However you can't let two of these
queries run at the same time unless you are prepared to retry insert
failures.