Skip site navigation (1) Skip section navigation (2)

Re: Modifiable sequence column?

From: Bruno Wolff III <bruno(at)wolff(dot)to>
To: Leif K-Brooks <eurleif(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Modifiable sequence column?
Date: 2005-08-17 04:48:50
Message-ID: 20050817044850.GB32756@wolff.to (view raw or flat)
Thread:
Lists: pgsql-novice
On Tue, Aug 16, 2005 at 21:01:41 -0400,
  Leif K-Brooks <eurleif(at)gmail(dot)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.

In response to

pgsql-novice by date

Next:From: Oliver KullmannDate: 2005-08-17 16:36:13
Subject: Re: how to rename an unnamed uniqueness constraint?
Previous:From: Leif K-BrooksDate: 2005-08-17 01:01:41
Subject: Modifiable sequence column?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group