MIME-Version: 1.0
References: 
 <CACqNvkOBzq8_z2+oygTd85-7U54mbWry8tfQLYU=MBMDTNRw1w@mail.gmail.com>
In-Reply-To: 
 <CACqNvkOBzq8_z2+oygTd85-7U54mbWry8tfQLYU=MBMDTNRw1w@mail.gmail.com>
From: Pedro Moraes <falqondev@gmail.com>
Date: Mon, 27 Jan 2025 10:18:29 -0300
Message-ID: 
 <CACqNvkPfEasOmKETeDTqm=vGOtR5yqAaMLL4kR_gmuZYPXfzWg@mail.gmail.com>
Subject: Insert on conflict do update fails with duplicate rows - there are no
 duplicates
To: pgsql-novice@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000003b2067062cafea06"
Archived-At: 
 <https://www.postgresql.org/message-id/CACqNvkPfEasOmKETeDTqm%3DvGOtR5yqAaMLL4kR_gmuZYPXfzWg%40mail.gmail.com>
Precedence: bulk

--0000000000003b2067062cafea06
Content-Type: text/plain; charset="UTF-8"

I managed to replicate the issue with the following data & tables attached
download link.

I am trying to insert from bugtest.temp_on_conflict_test into
bugtest.history, both tables have a primary key on history_id so there
cannot be duplicates

The insert query also uses distinct on (history_id)

INSERT INTO bugtest.history
(account_id,appo_id,account_name,resource_owner_name,resource_owner_id,resource_owner_user_id,resource_id,resource_type,preview,resource_date,history_id)

select distinct on (history_id) * from bugtest.temp_on_conflict_test limit
2
ON CONFLICT (history_id) DO UPDATE SET
account_id = excluded.account_id,
resource_owner_name = excluded.resource_owner_name,
resource_owner_user_id = excluded.resource_owner_user_id,
resource_owner_id = excluded.resource_owner_id,
preview = excluded.preview,
resource_date = excluded.resource_date,
account_name = excluded.account_name
RETURNING *

Reproducible dump:
https://limewire.com/d/2d598eff-f7c9-47d2-80d6-7e522ee1688c#ru2TRVilIqQdqmEjr2-_H4z67PHYe2JZAshDErA8umw

--0000000000003b2067062cafea06
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div class=3D"gmail_quote gmail_quote_container"><br><div =
dir=3D"ltr"><div>I managed to replicate the issue with the following data &=
amp; tables attached download link.</div><div><br></div><div>I am trying to=
 insert from
bugtest.temp_on_conflict_test=20

 into=20
bugtest.history, both tables have a primary key on history_id so there cann=
ot be duplicates</div><div><br></div><div>The insert query also uses distin=
ct on (history_id)<br><br>
<div>INSERT INTO bugtest.history (account_id,appo_id,account_name,resource_=
owner_name,resource_owner_id,resource_owner_user_id,resource_id,resource_ty=
pe,preview,resource_date,history_id)=20
</div><div>select distinct on (history_id) * from bugtest.temp_on_conflict_=
test limit 2
</div><div>ON CONFLICT (history_id) DO UPDATE SET=20
</div><div>account_id =3D excluded.account_id,
</div><div>resource_owner_name =3D excluded.resource_owner_name,
</div><div>resource_owner_user_id =3D excluded.resource_owner_user_id,
</div><div>resource_owner_id =3D excluded.resource_owner_id,
</div><div>preview =3D excluded.preview,
</div><div>resource_date =3D excluded.resource_date,
</div><div>account_name =3D excluded.account_name
</div>RETURNING *

<br></div><div><br>Reproducible dump: <br><a href=3D"https://limewire.com/d=
/2d598eff-f7c9-47d2-80d6-7e522ee1688c#ru2TRVilIqQdqmEjr2-_H4z67PHYe2JZAshDE=
rA8umw">https://limewire.com/d/2d598eff-f7c9-47d2-80d6-7e522ee1688c#ru2TRVi=
lIqQdqmEjr2-_H4z67PHYe2JZAshDErA8umw</a></div></div>
</div></div>

--0000000000003b2067062cafea06--