skip to Main Content

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


  1. 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.

    Login or Signup to reply.
  2. Drop the unique constraint, do your update, reinstate the constraint.

    Login or Signup to reply.
  3. 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

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search