Re: Discarding UNIQUE temporarily? - Mailing list pgsql-novice

From Tom Lane
Subject Re: Discarding UNIQUE temporarily?
Date
Msg-id 21862.1074498644@sss.pgh.pa.us
Whole thread Raw
In response to Discarding UNIQUE temporarily?  ("Timo" <siroco@suomi24.fi>)
List pgsql-novice
"Timo" <siroco@suomi24.fi> writes:
> create temp table foo (name text, rank int unique);
> ...
> update foo set rank = rank + 1 where rank > 2;

> But this, of course, won't do because the first update violates unique
> constraint which in my application is mandatory.

This should work according to the SQL spec, because UNIQUE constraints
are supposed to be tested as of the completion of a query.  Postgres
currently does uniqueness checking incrementally, which can fail as
you've observed.

> Is there any simple workaround for this apart from writing a function?

Not really :-(.  If you can identify a range of values that aren't
normally used in the table, you can do a horrid two-step kluge.  For
example, if there aren't normally any negative ranks:

    update foo set rank = -(rank + 1) where rank > 2;
    update foo set rank = -rank where rank < 0;

> Wouldn't this kind of update-extension be handy:
> update foo set rank = rank + 1 where rank > 2 order by rank desc;

No.  The correct fix is to make it behave per spec; there won't be a
lot of interest in introducing nonstandard language extensions to work
around the shortcoming ...

            regards, tom lane

pgsql-novice by date:

Previous
From: Bruno Wolff III
Date:
Subject: Re: query
Next
From: glenn
Date:
Subject: Re: Meta data about object in postgres