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.

• McNets: Please add table schema of tables A and B.
• Himanshu: I have created a minimal question to reproduce the error whole table has many columns but problem is when updating user_id column

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

• Himanshu: Thanks for your effort I will compare this with my original schema.