Re: Discarding UNIQUE temporarily?

"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