MIME-Version: 1.0
References: <CAEzWdqeKafRTLDSP-0Ffw8nijQPUXKpgaHxMXoqz0yEdad-2aA@mail.gmail.com>
 <cb623e32-4cba-4900-b379-dcc7e6da7023@cloud.gatewaynet.com>
In-Reply-To: <cb623e32-4cba-4900-b379-dcc7e6da7023@cloud.gatewaynet.com>
From: yudhi s <learnerdatabase99@gmail.com>
Date: Tue, 12 Nov 2024 00:20:07 +0530
Message-ID: <CAEzWdqcMnG8FuSVA_4OKPKwL0CC-LiOHeUx4sYSpScazvSBTXQ@mail.gmail.com>
Subject: Re: Duplicate key error
To: Achilleas Mantzios - cloud <a.mantzios@cloud.gatewaynet.com>
Cc: pgsql-general@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000008257ea0626a79219"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqcMnG8FuSVA_4OKPKwL0CC-LiOHeUx4sYSpScazvSBTXQ%40mail.gmail.com>
Precedence: bulk

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

On Mon, Nov 11, 2024 at 1:57=E2=80=AFPM Achilleas Mantzios - cloud <
a.mantzios@cloud.gatewaynet.com> wrote:

>
> > WITH source_data (col1, col2, col3.....col29) AS (VALUES ($1,
> > $2::date, $3::timestamptz, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13,
> > $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26,
> > $27::timestamptz, $28, $29::timestamptz)) MERGE INTO cpod.TAB1 AS
> > target USING source_data AS source ON target.ID =3D source.ID WHEN
> > MATCHED THEN UPDATE SET ....) WHEN NOT MATCHED THEN INSERT (...)
> > VALUES (....);
> >
> > Error:
> > ERROR: duplicate key value violates unique constraint "TAB1_pkey"
> > Detail: Key (ID, part_date)=3D(XXXXXXXXX, 2024-11-04) already exists.
>
> You have to use the whole composite unique key (including part_date)
> when matching against source_data .
>
> If you had uniqueness on "ID" and then added "part_name" to the key,
> you'd still had uniqueness.
>
> BUT, reversely, if you have uniqueness on the pair (ID, part_date) there
> is no guaranteed uniqueness on ID alone, hence your ERROR.
>
>
>
Actually the table is partitioned on column part_date which is why the
unique key is composite i.e. on ID and part_date. So in that case even we
merge on one of the column i.e. ID which is unique in itself, if any
duplicate value ID tries to be merged it should satisfy the MATCH criteria
and thus it should do a UPDATE on table TAB1 rather than going for NOMATCH
or INSERT. So in that case it should not error out with "duplicate key
violation". Is my understanding correct here?

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

<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 Mon, Nov 11, 2024 at 1:57=E2=80=AF=
PM Achilleas Mantzios - cloud &lt;<a href=3D"mailto:a.mantzios@cloud.gatewa=
ynet.com">a.mantzios@cloud.gatewaynet.com</a>&gt; wrote:<br></div><blockquo=
te class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px =
solid rgb(204,204,204);padding-left:1ex"><br>
&gt; WITH source_data (col1, col2, col3.....col29) AS (VALUES ($1, <br>
&gt; $2::date, $3::timestamptz, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13,=
 <br>
&gt; $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, <br>
&gt; $27::timestamptz, $28, $29::timestamptz)) MERGE INTO cpod.TAB1 AS <br>
&gt; target USING source_data AS source ON target.ID =3D source.ID WHEN <br=
>
&gt; MATCHED THEN UPDATE SET ....) WHEN NOT MATCHED THEN INSERT (...) <br>
&gt; VALUES (....);<br>
&gt;<br>
&gt; Error:<br>
&gt; ERROR: duplicate key value violates unique constraint &quot;TAB1_pkey&=
quot;<br>
&gt; Detail: Key (ID, part_date)=3D(XXXXXXXXX, 2024-11-04) already exists.<=
br>
<br>
You have to use the whole composite unique key (including part_date) <br>
when matching against source_data .<br>
<br>
If you had uniqueness on &quot;ID&quot; and then added &quot;part_name&quot=
; to the key, <br>
you&#39;d still had uniqueness.<br>
<br>
BUT, reversely, if you have uniqueness on the pair (ID, part_date) there <b=
r>
is no guaranteed uniqueness on ID alone, hence your ERROR.<br>
<br><br></blockquote><div><br></div><div>Actually the table is partitioned =
on column part_date which is why the unique key is composite i.e. on ID and=
 part_date. So in that case even we merge on one of the column i.e. ID whic=
h is unique in itself, if any duplicate value ID tries to be merged it shou=
ld satisfy the MATCH criteria and thus it should do a UPDATE on table=C2=A0=
TAB1 rather than going for NOMATCH or INSERT. So in that case it=C2=A0shoul=
d not error out with &quot;duplicate key violation&quot;. Is my understandi=
ng correct here?</div></div></div>

--0000000000008257ea0626a79219--