Updating a PostgreSQL table with a unique constraint-CodePudding

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.

CodePudding user response：

Is it possible to run the update below without getting a unique contraint violation and without modifying table tmp?

No, this is not possible. You would have to make the UNIQUE constraint DEFERRABLE:

    ...
    UNIQUE (c2, c3) DEFERRABLE
    ...

Then you could set it to deferred like this

SET CONSTRAINTS tmp_c2_c3_key DEFERRED;

and run your update command without a violation error. Note that you must use a transaction for SET CONSTRAINTS to work.

CodePudding user response：

Drop the unique constraint, do your update, reinstate the constraint.

CodePudding user response：

You can do 2 updates, one that will change your c3 values with other value than then previous ones like

UPDATE tmp
SET c3 = -c1
WHERE 1 = 1;

and then update your c3 value with the good one

UPDATE tmp
SET c3 = c1
WHERE 1 = 1;

It will work if all your values in c1 and c3 are positive