MIME-Version: 1.0
References: <CAEzWdqeKafRTLDSP-0Ffw8nijQPUXKpgaHxMXoqz0yEdad-2aA@mail.gmail.com>
 <CAH2-Wz=bSrxrEF_xsz+jLgiqQqE=n2_PZPe-GzRsSz6k6-Vr9A@mail.gmail.com>
In-Reply-To: <CAH2-Wz=bSrxrEF_xsz+jLgiqQqE=n2_PZPe-GzRsSz6k6-Vr9A@mail.gmail.com>
From: yudhi s <learnerdatabase99@gmail.com>
Date: Wed, 13 Nov 2024 02:08:45 +0530
Message-ID: <CAEzWdqcG8wFcVCcW-7YVsPWxb-9uzHcKNmyYOdLEZ6o1MHC8Aw@mail.gmail.com>
Subject: Re: Duplicate key error
To: Peter Geoghegan <pg@bowt.ie>
Cc: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000d918980626bd3460"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqcG8wFcVCcW-7YVsPWxb-9uzHcKNmyYOdLEZ6o1MHC8Aw%40mail.gmail.com>
Precedence: bulk

--000000000000d918980626bd3460
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Tue, Nov 12, 2024 at 1:35=E2=80=AFAM Peter Geoghegan <pg@bowt.ie> wrote:

> On Mon, Nov 11, 2024 at 12:41=E2=80=AFAM yudhi s <learnerdatabase99@gmail=
.com>
> wrote:
> > So it means it will ensure no duplication happens for ID values, but
> still we are seeing "duplicate key" error. So what is the possible reason
> here or are we encountering any buggy behaviour here?
>
> MERGE doesn't actually make any promises about not getting unique
> violations. Only ON CONFLICT DO UPDATE (and ON CONFLICT DO NOTHING)
> make such a promise. That's the main reason why Postgres supports
> both.
>
>
Okay. But here in this Merge statement it should first compare the ON
clause which is the value of ID column and if its exists in the
target table then its a MATCH which means it will do the UPDATE and if
its not available in the target table then its a NOT MATCH and it will do
the INSERT, so i am wondering at what exact situation it will throw
duplicate key error. Also the WITH clause will only pick one record at a
time and run the MERGE, so it will only merge one record at a time and then
commit. Can you share your thoughts on how exactly this merge query can
possibly cause the duplicate key error?

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

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=
=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Tue, Nov 12, 2024=
 at 1:35=E2=80=AFAM Peter Geoghegan &lt;<a href=3D"mailto:pg@bowt.ie" targe=
t=3D"_blank">pg@bowt.ie</a>&gt; wrote:<br></div><blockquote class=3D"gmail_=
quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,=
204);padding-left:1ex">On Mon, Nov 11, 2024 at 12:41=E2=80=AFAM yudhi s &lt=
;<a href=3D"mailto:learnerdatabase99@gmail.com" target=3D"_blank">learnerda=
tabase99@gmail.com</a>&gt; wrote:<br>
&gt; So it means it will ensure no duplication happens for ID values, but s=
till we are seeing &quot;duplicate key&quot; error. So what is the possible=
 reason here or are we encountering any buggy behaviour here?<br>
<br>
MERGE doesn&#39;t actually make any promises about not getting unique<br>
violations. Only ON CONFLICT DO UPDATE (and ON CONFLICT DO NOTHING)<br>
make such a promise. That&#39;s the main reason why Postgres supports<br>
both.<br><br></blockquote><div><br></div><div>Okay. But here in this Merge =
statement it should first compare the ON clause which is the value of ID co=
lumn and if its=C2=A0exists in the target=C2=A0table then its a MATCH which=
 means it will do the UPDATE and if its=C2=A0not available in the target ta=
ble then its a NOT MATCH and it will do the INSERT, so i am wondering at wh=
at exact situation it will throw duplicate key error. Also the WITH clause =
will only pick one record at a time and run the MERGE, so it will only merg=
e one record at a time and then commit. Can you share your thoughts on how =
exactly=C2=A0this merge query can possibly cause the=C2=A0duplicate key err=
or?</div></div></div>
</div>

--000000000000d918980626bd3460--