Increase unique value in few row

Question

I have the table create as

create table public.test
(
    val int unique not null
)

This table contains some rows

|   Val  |
|--------|
|1       |
|2       |
|3       |
|4       |
|5       |
|6       |
|7       |
|8       |
|9       |

I wanna increment all values greater then 5 (using PostgreSQL). But if try update public.test set val=val+1 where val > 5 i will get exception:

ERROR: duplicate key value violates unique constraint "test_val_key" DETAIL: Key (val)=(7) already exists.

How i can do this?

user_330315 · Answer

If you need that on regular basis you should create the constraint as deferrable:

create table test
(
    val int not null
);
alter table test
   add constraint unique_value unique(val)
   deferrable initially immediate;

With the above constraint the following update works just fine:

update test set val=val+1 where val > 5;

user_908936 · Answer

Option without deferrable initially immediate

update test t1
set val=t2.val+1
from (select val from test order by val desc) t2
where t2.val > 5 and t1.val=t2.val

Online example with checking that sorting is saved: http://rextester.com/FAU54991

user_1144035 · Answer

One option is to drop the unique constraint and then add it back.

Another option is a hack:

update public.test
    set val = - val
    where val > 5;

update public.test
    set val = (- val) + 1
    where val < 0;