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!