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)

Answer 1

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.

Answer 2

you can use update on existing record/row 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