Message-ID: <dd8a7e07edaa6efac790d6190a1683cd996eb847.camel@cybertec.at>
Subject: Re: Insert on conflict do update fails with duplicate rows - there
 are no duplicates
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Pedro Moraes <falqondev@gmail.com>, pgsql-novice@lists.postgresql.org
Date: Mon, 27 Jan 2025 16:04:56 +0100
In-Reply-To: 
 <CACqNvkPfEasOmKETeDTqm=vGOtR5yqAaMLL4kR_gmuZYPXfzWg@mail.gmail.com>
References: 
	<CACqNvkOBzq8_z2+oygTd85-7U54mbWry8tfQLYU=MBMDTNRw1w@mail.gmail.com>
	 <CACqNvkPfEasOmKETeDTqm=vGOtR5yqAaMLL4kR_gmuZYPXfzWg@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/dd8a7e07edaa6efac790d6190a1683cd996eb847.camel%40cybertec.at>
Precedence: bulk

On Mon, 2025-01-27 at 10:18 -0300, Pedro Moraes wrote:
>=20
> I managed to replicate the issue with the following data & tables attache=
d download link.
>=20
> I am trying to insert from bugtest.temp_on_conflict_test into bugtest.his=
tory, both tables have a primary key on history_id so there cannot be dupli=
cates
>=20
> The insert query also uses distinct on (history_id)
>=20
> INSERT INTO bugtest.history (account_id,appo_id,account_name,resource_own=
er_name,resource_owner_id,resource_owner_user_id,resource_id,resource_type,=
preview,resource_date,history_id)=20
> select distinct on (history_id) * from bugtest.temp_on_conflict_test limi=
t 2
> ON CONFLICT (history_id) DO UPDATE SET=20
> account_id =3D excluded.account_id,
> resource_owner_name =3D excluded.resource_owner_name,
> resource_owner_user_id =3D excluded.resource_owner_user_id,
> resource_owner_id =3D excluded.resource_owner_id,
> preview =3D excluded.preview,
> resource_date =3D excluded.resource_date,
> account_name =3D excluded.account_name
> RETURNING *
>=20
> Reproducible dump:=20
> https://limewire.com/d/2d598eff-f7c9-47d2-80d6-7e522ee1688c#ru2TRVilIqQdq=
mEjr2-_H4z67PHYe2JZAshDErA8umw

I get this error message:

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

The problem is that you are using "SELECT *", which means that you will get=
 the columns
in the order they were defined in the table.  So you end up inserting
"temp_on_conflict_test.account_name" into "history.history_id", which cause=
s the error,
because all rows in "temp_on_conflict_test" have the same value in that col=
umn.

Lessons to learn:
- don't use "SELECT *"
- use more appropriate data types
  In the case at hand, using type "uuid" for "history_id" would have got yo=
u a more
  understandable error message.

Yours,
Laurenz Albe