How do you make a update idempotent in a primary key in postgresql?

Question

I have this error:

SQL Error [23505]: ERROR: duplicate key value violates unique constraint "name_pkey" Detail: Key (status_id, type)=(0, 1) already exists.

When I execute this update script:

update schema.table_name
    set type = '1'
    where status_id in ('AT', '0', '1',);

There is already a row in the table with status 0 and type 1, and you have a unique key that forbids duplicates. How do you want to handle this? — GMB

Gordon Linoff Gordon Linoff · Accepted Answer · 2020-11-25T12:30:26

The primary key is on (status_id, type), which means that you already have rows for one more more of the following pairs:

AT    1
0     1
1     1

So, the update doesn't work. You can ignore the key by filtering out potential conflicts:

update schema.table_name t
    set type = '1'
    from (values ('AT'), ('0'), ('1')) v(status_id)
    where t.status_id = v.status_id and
          not exists (select 1
                      from schema.table_name t2
                      where t2.status_id = t.status_id and t2.type = '1'
                     );

This simply ignores the update that would have occurred.

How do you make a update idempotent in a primary key in postgresql?

1 Answers