How to ignore/skip duplicate (index) on update

0
votes

I´ve the following table with two columns. The two columns are the combined index key of the table:

ID1    ID2
A    X
A    Y
A    Z
B    Z

Now I need to do the following:

UPDATE table SET ID2=X WHERE ID2=Z

As the table has a combined key (ID1 + ID2) this leads to a "duplicate key value violates unique constraint" exception cause there is already an "A-X" combination and the update for row "A-Z" violates that key.

So my question is: Is there in Postgres an option to skip on duplicate key? Or do you have any other hint for me?

Final state of the table should be:

ID1    ID2
A    X
A    Y
B    X
2
postgresql
What should be the state of the table after the operation has finished? - Laurenz Albe
There should be only one A-X left. I´ve added an example above - Michael

2 Answers

0
votes

One solution I can think of, is to define the primary key as deferrable and then do the update and delete in a single statement:

create table t1 (
  id1 text, 
  id2 text, 
  primary key (id1, id2) deferrable
);

Then you can do the following:

with changed as (
  UPDATE t1 
    SET ID2='X' 
  WHERE ID2='Z'
  returning id1, id2
)
delete from t1
where (id1, id2) in (select id1, id2 from changed)

The DELETE does not see the new row that has been created by the update, but it sees the "old" one, so only the conflicting one(s) are deleted.

Online example

0
votes

I realize that under normal conditions we prefer a single statement, but at times 2 may be better. This may very well be one of those. In order to implement the solution by @a_horse_with_no_name it will be necessary to drop and recreate the PK. Not a desirable move. So a 2 statement solution which does not require playing around with the PK.

do $$
begin
   delete 
     from tbl t1
    where t1.id2 = 'Z'
      and exists (
                   select null
                     from tbl t2 
                    where t1.id1 = t2.id1
                      and t2.id2 = 'X'
                 );

update tbl 
   set id2 = 'X'
 where id2 = 'Z';

 end; 
 $$;

The advantage of the DO block being that if for some reason the second statement fails, the action of the first is rolled back. So the database remains in a consistent state.