Postgresql update multiple rows and skip row on error

Question

I have a query which updates single column of multiple records using column value from another table. For example below are the tables--

Table A

+-----------------+---------------------+------+
| id(primary_key) | user_id(unique_key) | f_id |
+-----------------+---------------------+------+
| 1               | 1                   | 11   |
+-----------------+---------------------+------+
| 2               | 3                   | 22   |
+-----------------+---------------------+------+

Table B

+----+------+
| id | f_id |
+----+------+
| 1  | 11   |
+----+------+
| 3  | 22   |
+----+------+

Now I tried to update user_id column for all records in Table A using id column from Table B on basis of f_id column which is common using below query.

UPDATE 
    A
SET 
    user_id = B.id
FROM 
    B
WHERE A.f_id = B.f_id

user_id column in Table A is unique which generates an error on bulk update as:

ERROR: duplicate key value violates unique constraint "A_user_id_key" DETAIL: Key (user_id)=(221207) already exists.

But when I update this particular record using below query

UPDATE 
    A
SET 
    user_id = 221207
FROM 
    B
WHERE A.f_id = B.f_id

it executes successfully without any error. Only bulk updates shows that error. I don't get what is going wrong.

Answer 1

It seems there is a UNIQUE INDEX on table A(user_id) and you're trying to assign an existing value to more than one row.

Have a look at the next example:

create table a (id int, user_id int, f_id int);
create table b (id int, f_id int);
create unique index A_user_id_key on a(user_id);

insert into a 
values (1, 1, 11),
       (2, 3, 22),
       (3, 2, 33),
       (4, 5, 44 );

insert into b
values (1, 11),
       (3, 22),
       (3, 33), --<<<< there is another row with user_id = 3
       (4, 44);

When I try to update using your current query:

update a
set    user_id = b.id
from   b
where  a.f_id = b.f_id;

It returns same error message:

ERROR: duplicate key value violates unique constraint "a_user_id_key"
DETAIL: Key (user_id)=(3) already exists.

You could solve by avoiding existing values:

update a
set    user_id = b.id
from   b
where  a.f_id = b.f_id
and    not exists(select 1 from a where a.user_id = b.id);

This is the result:

select * from a;

id | user_id | f_id
-: | ------: | ---:
 1 |       1 |   11
 2 |       3 |   22
 3 |       2 |   33
 4 |       4 |   44

db<>fiddle here

But I'll suggest to check wich are the duplicate rows using next query:

select a.*
from   a
join   b
on     a.f_id = b.f_id
and    exists(select 1 from a where user_id = b.id and f_id <> b.f_id);

id | user_id | f_id
-: | ------: | ---:
 3 |       2 |   33

db<>fiddle here