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?