Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3776.700.51\))
Subject: Re: Insert works but fails for merge
From: Alban Hertroys <haramrae@gmail.com>
In-Reply-To: <CAEzWdqfLi-JtgZPhArq-6hAcSe9RnjN62LbeS8rVVSAiQOiOtg@mail.gmail.com>
Date: Sun, 11 Aug 2024 12:09:44 +0200
Cc: Adrian Klaver <adrian.klaver@aklaver.com>,
 David G Johnston <david.g.johnston@gmail.com>,
 pgsql-general <pgsql-general@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <A0C7725A-C82D-4B62-A485-D1C4CE7C5A8E@gmail.com>
References: <CAEzWdqc=QKpfFU3jNSoBrZ37y727GK2w2rSQ2qJCEGJDT5Pm6Q@mail.gmail.com>
 <37e09717-f121-4192-b152-18df17713414@aklaver.com>
 <CAEzWdqeW+yVwb2v8Vy-t9DC69_cMhUWTQqVBoL7_9qDzhBe8=Q@mail.gmail.com>
 <9dc702ca-8bb3-442e-bd2b-12abe81a84d0@aklaver.com>
 <CAEzWdqfLi-JtgZPhArq-6hAcSe9RnjN62LbeS8rVVSAiQOiOtg@mail.gmail.com>
To: yudhi s <learnerdatabase99@gmail.com>
Archived-At: <https://www.postgresql.org/message-id/A0C7725A-C82D-4B62-A485-D1C4CE7C5A8E%40gmail.com>
Precedence: bulk


> On 10 Aug 2024, at 22:23, yudhi s <learnerdatabase99@gmail.com> wrote:
> On Sat, Aug 10, 2024 at 8:22=E2=80=AFPM Adrian Klaver =
<adrian.klaver@aklaver.com> wrote:
> > MERGE INTO tab1 AS target
> > USING (VALUES ('5efd4c91-ef93-4477-840c-a723ae212d99', 123,=20
> > '2024-08-09T11:33:49.402585600Z','2024-08-09T11:33:49.402585600Z')) =
AS=20
> > source(id, mid,txn_timestamp, cre_ts)
> > ON target.id <http://target.id> =3D source.id <http://source.id>
> > WHEN MATCHED THEN
> > UPDATE SET mid  =3D source.mid
> > WHEN NOT MATCHED THEN
> > INSERT (id, mid, txn_timestamp, cre_ts)
> >      VALUES (source.id <http://source.id>,source.mid,=20
> >   source.txn_timestamp, source.cre_ts);
>=20
> Actually , as per the business logic , we need to merge on a column =
which is not unique or having any unique index on it.

Then how is the database supposed to determine which of those duplicate =
rows it should update? In the best case, it would update all of the =
duplicates with the same values, which usually is not what you want.

> It's the leading column of a composite unique key though.

Which could be unique of itself, I suppose that isn=E2=80=99t the case =
here?

In that case, IMHO your best course of action is to do something about =
those duplicates first.

> And in such scenarios the "INSERT.... ON CONFLICT" will give an error. =
So we are opting for a merge statement here, which will work fine with =
the column being having duplicate values in it.

I=E2=80=99m not so sure about that claim=E2=80=A6

At least on MSSQL, MERGE has this requirement: "A MERGE statement cannot =
UPDATE/DELETE the same row of the target table multiple times.=E2=80=9D. =
I=E2=80=99ve seen that as an error message on occasion.

The MERGE documentation for PostgreSQL says this: "You should ensure =
that the join produces at most one candidate change row for each target =
row.=E2=80=9D, which also seems to imply that you shouldn=E2=80=99t have =
duplicates.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.