create temp table foo (name text, rank int unique);

insert into foo values ('Joe', 1);
insert into foo values ('Matt', 2);
insert into foo values ('Bill', 3);
insert into foo values ('John', 4);

I'd need to alter the ranks with something like this:

update foo set rank = rank + 1 where rank > 2;
update foo set rank = 3 where name = 'John';

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

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

Can I somehow force the update to happen in the order of a subquery?

update foo set rank = rank + 1 where rank in
(select rank from foo where rank > 2 order by rank desc);

Wouldn't this kind of update-extension be handy:

update foo set rank = rank + 1 where rank > 2 order by rank desc;

Regards,
Timo


I'd also like to thank the PG team for developing this great application.
I'm just beginning to realize the amount if time and efforts you must have
spent on this!

"Timo" <siroco (AT) suomi24 (DOT) fi> writes:
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.

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;

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html