I have a table with a unique constraint
CREATE temp TABLE tmp (
c1 int,
c2 text,
c3 int,
UNIQUE (c2, c3)
);
insert into tmp (c1, c2, c3)
values (1, 'a', 2),
(2, 'a', 1)
Is it possible to run the update below without getting a unique contraint violation and without modifying table tmp
?
update tmp
set c3 = c1
where 1=1
ERROR: duplicate key value violates unique constraint "tmp_c2_c3_key". Detail: Key (c2, c3)=(a, 1) already exists.
3
Answers
No, this is not possible. You would have to make the
UNIQUE
constraintDEFERRABLE
:Then you could set it to deferred like this
and run your update command without a violation error. Note that you must use a transaction for
SET CONSTRAINTS
to work.Drop the unique constraint, do your update, reinstate the constraint.
You can do 2 updates, one that will change your c3 values with other value than then previous ones like
and then update your c3 value with the good one
It will work if all your values in c1 and c3 are positive