Increase unique value in few row [duplicate]

This question already has an answer here:

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:

update public.test set val=val+1 where val > 5

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

How i can do this?

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

3 Answers
3

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;


For the intended update to work, it's not necessary to mark the constraint as initially deferred or to change the constraints to deferrable in the session.

initially deferred

With the above constraint the following update works just fine:

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


Online example: http://rextester.com/KXI21597

Option without deferrable initially immediate

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: http://rextester.com/HVZRC8695

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

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;