"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