Question 90787 in PostgreSQL Question posted in
The official documentation can be found here.

Updating a PostgreSQL table with a unique constraint

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.
Tags:

3

Answers


  1. 0

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

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

    Login or Signup to reply.
  3. 0

    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