postgresql on conflict-cannot affect row a second time

Question

I have a table, i have auto numbering/sequence on data_id

tabledata
---------
data_id   [PK]
data_code [Unique]
data_desc

example code:

insert into tabledata(data_code,data_desc) values(Z01,'red')
on conflict (data_code) do update set data_desc=excluded.data_desc

works fine, and then i insert again

insert into tabledata(data_code,data_desc) values(Z01,'blue')
on conflict (data_code) do update set data_desc=excluded.data_desc

i got this error

[Err] ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

this is my real code

insert into psa_aso_branch(branch_code,branch_desc,regional_code,status,created_date,lastmodified_date) 
    (select branch_code, branch, kode_regional, 
    case when status_data='Y' then true 
    else false end, current_date, current_date 
    from branch_history) on conflict (branch_code) do
    update set branch_desc = excluded.branch_desc, regional_code = excluded.regional_code,status = (case when excluded.status='Y' then true else false end), created_date=current_date, lastmodified_date=current_date;

working fine on first, but not the next one (like the example i give you before)

`data_desc` is the only not unique attribute, why you `set data_desc=excluded.data_desc`?.. — Vao Tsun, Mar 24 '17 at 08:09
Your queries have syntax errors & does not target the same table. After fixing these, they're working: http://rextester.com/NMCXR16182 -- However, your error message suggests that you are executing different ones: http://stackoverflow.com/questions/35949877/how-to-include-excluded-rows-in-returning-from-insert-on-conflict — pozs, Mar 24 '17 at 08:43
already edited the code, its working fine on your link but not mine, yeah my code actually a little bit different but still should have the same concept, i will edit my post — Alexander Chandra, Mar 24 '17 at 08:56
@AlexanderChandra your example contains `Z01` which causes the syntax error. It should be `'Z01'` (when the column is string-like). -- If you use `INSERT INTO ... SELECT` then you should use `SELECT DISTINCT ON (unique_columns_list) ...` to avoid the error message you get (but this is already mentioned in the question I linked before). — pozs, Mar 24 '17 at 10:40
Yeah my subquery return 2 values, no wonder it say "cannot affect row a second time" — Alexander Chandra, Mar 25 '17 at 06:38

score 17 · Answer 1 · edited Nov 22 '22 at 18:09

17

You can use update on the existing record/row, and not on row you are inserting.

Here update in on conflict clause applies to row in excluded table, which holds row temporarily.

In the first case record is inserted since there is no clash on data_code and update is not executed at all.

In the second insert you are inserting Z01 which is already inserted as data_code and data_code is unique.

The excluded table still holds the duplicate value of data_code after the update, so the record is not inserted. In update set data_code have to be changed in order to insert record properly.

edited Nov 22 '22 at 18:09

Martin Tournoij

26,737
24
105
146

answered Mar 24 '17 at 08:15

harsha kumar Reddy

1,251
1
20
32

1

what do you mean " and since you have not inserted record you can not update data_desc there "? And how to fix it? – Alexander Chandra Mar 24 '17 at 08:29
26

@AlexanderChandra just ran into the same problem. just to clarify why you get the error: if you have an existing record in your db wtih unique id 123, then - u try to insert a new record with id 123 -> the 'on conflict clause' will work. but if on the same insert you try and insert 2 records with the same unique id (123), the 'on conflict' clause wont resolve such cases and will raise the exception you got. – zivaricha Jul 22 '20 at 13:10

score 12 · Accepted Answer · answered Mar 31 '21 at 07:29

12

I have been stuck on this issue for about 24 hours.

It is weird when I test the query on cli and it's works fine. It is working fine when I make an insertion using one data row. This errors only appear when I'm using insert-select.

It is not mostly because of insert-select problem. It is because the select rows is not unique. This will trigger the CONFLICT for more than once.

Thanks to @zivaricha comment. I experiment from his notes. Just that its hard to understand at first.

Solution: Using distinct to make sure the select returns unique result.

answered Mar 31 '21 at 07:29

Yakob Ubaidi

1,846
2
20
23

2

I too hoped a select distinct approach would work, but I am still seeing the same error. Any thoughts? – Snehaa Ganesan Jul 21 '21 at 19:34
4

Added `LIMIT 1` before `ON CONFLICT` statement and everything works fine – SimBioT Jan 12 '22 at 17:41
Seems like adding `LIMIT 1` seems to work if the 'next conflicting' value occurs only once. Tested, only one row, out of many rows, is inserted. – Laenka-Oss May 18 '23 at 19:55

score 5 · Answer 3 · answered Dec 01 '21 at 12:03

This error comes when the duplicacy occurs multiple times in the single insertion
for example you have column a , b , c and combination of a and b is unique and on duplicate you are updating c. Now suppose you already have a = 1 , b = 2 , c = 3 and you are inserting a = 1 b = 2 c = 4 and a = 1 b = 2 c = 4
so means conflict occurs twice so it cant update a row twice

score 5 · Answer 4 · answered Mar 21 '22 at 01:43

We can find the error message from the source code, which we can simply understand why we got ON CONFLICT DO UPDATE command cannot affect row a second time.

In the source code of PostgreSQL at src/backend/executor/nodeModifyTable.c and the function of ExecOnConflictUpdate(), we can find this comment:

This can occur when a just inserted tuple is updated again in the same command. E.g. because multiple rows with the same conflicting key values are inserted.
This is somewhat similar to the ExecUpdate() TM_SelfModified case. We do not want to proceed because it would lead to the same row being updated a second time in some unspecified order, and in contrast to plain UPDATEs there's no historical behavior to break.

As the comment said, we can not update the row which we are inserting in INSERT ... ON CONFLICT, just like:

postgres=#  CREATE TABLE t (id int primary key, name varchar);

postgres=#  INSERT INTO t VALUES (1, 'smart'), (1, 'keyerror') 
postgres=#  ON CONFLICT (id) DO UPDATE SET name = 'Buuuuuz';
ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

Remember, the executor of postgresql is a volcano model, so it will process the data we insert one by one. When we process to (1, 'smart'), since the table is empty, we can insert normally. When we get to (1, 'keyerror'), there is a conflict with the (1, 'smart') we just inserted, so the update logic is executed, which results in updating our own inserted data, which PostgreSQL doesn't allow us to do.

Similarly, we cannot update the same row of data twice:

postgres=# DROP TABLE IF EXISTS t;
postgres=# CREATE TABLE t (id int primary key, name varchar);

postgres=# INSERT INTO t VALUES (1, 'keyerror'), (1, 'buuuuz') 
postgres=# ON CONFLICT (id) DO UPDATE SET name = 'Buuuuuuuuuz';
ERROR:  ON CONFLICT DO UPDATE command cannot affect row a second time
HINT:  Ensure that no rows proposed for insertion within the same command have duplicate constrained values.

Your second example is the same as the first, did you mean to INSERT a row first with the ID of 1? — Malvineous, Jun 29 '22 at 16:08

score 3 · Answer 5 · answered Mar 10 '21 at 07:12

3

I think what is happening here when you do an update on conflict, it does an update that re conflicts again and then throws that error

answered Mar 10 '21 at 07:12

Isaac Sekamatte

5,500
1
34
40

postgresql on conflict-cannot affect row a second time

5 Answers5