MIME-Version: 1.0
From: yudhi s <learnerdatabase99@gmail.com>
Date: Mon, 11 Nov 2024 11:10:48 +0530
Message-ID: <CAEzWdqeKafRTLDSP-0Ffw8nijQPUXKpgaHxMXoqz0yEdad-2aA@mail.gmail.com>
Subject: Duplicate key error
To: pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000b6199906269c8ba9"
Archived-At: <https://www.postgresql.org/message-id/CAEzWdqeKafRTLDSP-0Ffw8nijQPUXKpgaHxMXoqz0yEdad-2aA%40mail.gmail.com>
Precedence: bulk

--000000000000b6199906269c8ba9
Content-Type: text/plain; charset="UTF-8"

We have a merge query as below for a partition table which is range
partitioned on a truncated date column 'part_date'. And the only unique key
in this table is a composite primary key on (id, part_date). And this merge
queries ON condition is based on one of the columns i.e ID which is the
leading column of the PK.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?

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 = 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)=(XXXXXXXXX, 2024-11-04) already exists.

Regards
Yudhi

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

<div dir=3D"ltr">We have a merge query as below for a partition table which=
 is range partitioned on a truncated date column &#39;part_date&#39;. And t=
he only unique key in this table is a composite primary key on (id, part_da=
te). And this merge queries ON condition is based on one of the columns i.e=
 ID which is the leading column of the PK.So it means it will ensure no dup=
lication happens for ID values, but still we are seeing &quot;duplicate key=
&quot; error. So what is the possible reason here or are we encountering an=
y buggy behaviour here?<br><br><font face=3D"monospace">WITH source_data (c=
ol1, 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)) MERG=
E INTO cpod.TAB1 AS target USING source_data AS source ON target.ID =3D sou=
rce.ID WHEN MATCHED THEN UPDATE SET ....) WHEN NOT MATCHED THEN INSERT (...=
) VALUES (....);<br><br>Error: <br>ERROR: duplicate key value violates uniq=
ue constraint &quot;TAB1_pkey&quot;<br>Detail: Key (ID, part_date)=3D(XXXXX=
XXXX, 2024-11-04) already exists.</font><br><br>Regards<div>Yudhi<br><br></=
div></div>

--000000000000b6199906269c8ba9--