Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sctpq-00BGPC-4Z for pgsql-general@arkaria.postgresql.org; Sat, 10 Aug 2024 21:41:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sctpm-00AcIp-Ut for pgsql-general@arkaria.postgresql.org; Sat, 10 Aug 2024 21:41:10 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sctpm-00AcIb-FD for pgsql-general@lists.postgresql.org; Sat, 10 Aug 2024 21:41:10 +0000 Received: from mail-qv1-xf2a.google.com ([2607:f8b0:4864:20::f2a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sctph-004Ba1-Fi for pgsql-general@lists.postgresql.org; Sat, 10 Aug 2024 21:41:09 +0000 Received: by mail-qv1-xf2a.google.com with SMTP id 6a1803df08f44-6b7a8cada97so18884156d6.3 for ; Sat, 10 Aug 2024 14:41:05 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1723326063; x=1723930863; darn=lists.postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=JyxychC+moo2O6mbhTpUZ+0JljR4y0EK3D6nmIQ1kHM=; b=bQBuUMNuV+Uz+Tit4qi6aJ4i3qt5cI9SrLqftRsLP+Zm/K1zs5ArDgBZdmH1vXztVL 0kM4ID+fWDcNodFzxuMcxPhEm/686EfI9+2naePyOTxnlSleNxqfvcwVj3KzFZ7e342P ZN+KMJ0xAF/wwi5+By1LYezGcpE6FEJyAUW/oNaj6Fpw1XQPe6xO+YY3Otp/FPfPX6/p mzZomvmugg3VIkAO4j8cGrWZIL1sLu/uRXO3WFbD7Mb9uafJWSfhKGTK4goOGREo0ZJV SO2aXKHKc3jqPwftK10lGYOZNWyq16laSwB2swAMM37KOkKMpsWt+/jdtrtHZjTTvL0F 76Kg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1723326063; x=1723930863; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=JyxychC+moo2O6mbhTpUZ+0JljR4y0EK3D6nmIQ1kHM=; b=knDDyljyXbb9Hz5+UMualHbJUbkE8IsbfgiPsShvcjOkkbXbEymvllHkJPhQZM8tv1 qC4el7mbIwAQHhX+Ai4JC+IbtewC8fb3ERn4fRd3k2r301C4fLIKuleJpqOvgGeBSnlY UeH3JWAmOskj428d9auC++8bOYp6J9m/kIE5e8WQ41eM2vueIzvdOKVDAJAEOndxrghU oUAIpKbEv0KdKRQztynGWJ6HC6Er6zuE01mJFAWH15332mXxQv8/ZC5TKM0AMNvF2Drm G/Mr0U/jbn5E5DWFLfslDpxM125kldfHXSG0I63Rdgr0dQurk+fXgo1FtecX3lYet1y7 NAng== X-Forwarded-Encrypted: i=1; AJvYcCVNZmTgRZuAYlP07hu8rgy/q0xtVXV+vadsJCLG63MZxQoHXrv20HBL7uiuHo/K2NwP0y3KzMOLePaRKV98cIRTlbIfqa4ZdpRylUK+aSxX5M3K X-Gm-Message-State: AOJu0Yxu3XqExFLcHKyLmI3DJmScm1Hbu7OgQRsZ1dgGCOEMsuE2xYAV 7mJrGGD9zinyWeT9MPPUiQmtdBADWtET4YwFEr55pLrUkDgIxk/NyozAL8n78LFEJdwiIGSSry3 RfVsdJOwcrRK5ucB+klERSlXm7zU= X-Google-Smtp-Source: AGHT+IFfo31eRXlrwaGZST34yn7OFFcpG+HgSHxCh7mKCFTVqa5V30ZoIPT0ejydeTPKorxnejEzjv3/eobfd8W6Y8s= X-Received: by 2002:a05:6214:5681:b0:6b7:a3da:1cdb with SMTP id 6a1803df08f44-6bd78d5a4bbmr60285446d6.21.1723326063527; Sat, 10 Aug 2024 14:41:03 -0700 (PDT) MIME-Version: 1.0 References: <37e09717-f121-4192-b152-18df17713414@aklaver.com> <9dc702ca-8bb3-442e-bd2b-12abe81a84d0@aklaver.com> In-Reply-To: From: yudhi s Date: Sun, 11 Aug 2024 03:10:52 +0530 Message-ID: Subject: Re: Insert works but fails for merge To: Adrian Klaver Cc: "David G. Johnston" , pgsql-general Content-Type: multipart/alternative; boundary="000000000000d94ea5061f5b1d52" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000d94ea5061f5b1d52 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Apology for the confusion. The other column is the txn_timestamp in the composite unique key, which is also the partition key. But yes we cant use both in the ON clause because of certain business requirements. We realized it late. And that's why "on conflict " We are unable to use. On Sun, 11 Aug, 2024, 2:57 am Adrian Klaver, wrote: > On 8/10/24 13:23, yudhi s wrote: > > > > > > On Sat, Aug 10, 2024 at 8:22=E2=80=AFPM Adrian Klaver > > wrote: > > > > > > > > Why not use INSERT ... ON CONFLICT instead of MERGE? > > > > > > > > MERGE INTO tab1 AS target > > > USING (VALUES ('5efd4c91-ef93-4477-840c-a723ae212d99', 123, > > > > > '2024-08-09T11:33:49.402585600Z','2024-08-09T11:33:49.402585600Z')) > AS > > > source(id, mid,txn_timestamp, cre_ts) > > > ON target.id > > =3D 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 > >,source.mid, > > > source.txn_timestamp, source.cre_ts); > > > > > > > > Actually , as per the business logic , we need to merge on a column > > which is not unique or having any unique index on it. It's the leading > > column of a composite unique key though. 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. > > > Alright it's official I am confused. > > You started with: > > WITH source_data (id, mid, txn_timestamp, cre_ts) AS ( > VALUES ('5efd4c91-ef93-4477-840c-a723ae212d66', 123, > '2024-08-09T11:33:49.402585600Z', '2024-08-09T11:33:49.402585600Z') > ) > INSERT INTO tab1 (id, mid, txn_timestamp, cre_ts) > SELECT id, mid, txn_timestamp, cre_ts > FROM source_data > ON CONFLICT (id) DO UPDATE > SET mid =3D EXCLUDED.mid, > txn_timestamp =3D EXCLUDED.txn_timestamp, > cre_ts =3D EXCLUDED.cre_ts; > > That implied that id was unique in of itself. As side note you called it > a merge, which it is not as in MERGE. At this point I got off track > thinking of MERGE. > > Then you went to the below which is a merge: > > MERGE INTO tab1 AS target > USING (VALUES ('5efd4c91-ef93-4477-840c-a723ae212d99', 123, > '2024-08-09T11:33:49.402585600Z','2024-08-09T11:33:49.402585600Z')) AS > source(id, mid,txn_timestamp, cre_ts) > ON target.id =3D 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,source.mid, source.txn_timestamp, source.cre_ts); > > The question I have now is if id is part of a composite UNIQUE index on > this: > > CREATE TABLE tab1 ( > id varchar(100) , > mid INT, > txn_timestamp TIMESTAMPTZ NOT NULL, > cre_ts TIMESTAMPTZ NOT NULL > ) PARTITION BY RANGE (txn_timestamp); > > Then what is the other column in the UNIQUE index? > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > > --000000000000d94ea5061f5b1d52 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Apology for the confusion. The other column is the txn_ti= mestamp in the composite unique key,=C2=A0 which is also the partition key.= =C2=A0

But yes we cant use bot= h in the ON clause because of certain business requirements. We realized it= late. And that's why "on conflict " We are unable to use.=C2= =A0

On Sun, 11 Aug, 2024, 2:57 am Adrian Klaver, <adrian.klaver@aklaver.com> wrote:
On 8/10/24 13:23, yudhi s wrote:
>
>
> On Sat, Aug 10, 2024 at 8:22=E2=80=AFPM Adrian Klaver <adria= n.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>
>
>=C2=A0 =C2=A0 =C2=A0Why not use INSERT ... ON CONFLICT instead of MERGE= ?
>
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0 > MERGE INTO tab1 AS target
>=C2=A0 =C2=A0 =C2=A0 > USING (VALUES ('5efd4c91-ef93-4477-840c-a= 723ae212d99', 123,
>=C2=A0 =C2=A0 =C2=A0 >
>=C2=A0 =C2=A0 =C2=A0'2024-08-09T11:33:49.402585600Z','2024-= 08-09T11:33:49.402585600Z')) AS
>=C2=A0 =C2=A0 =C2=A0 > source(id, mid,txn_timestamp, cre_ts)
>=C2=A0 =C2=A0 =C2=A0 > ON target.id <http://target.id= > <http://target.id
>=C2=A0 =C2=A0 =C2=A0<http://target.id>> =3D source.id<= /a> <http://source.id> <http://source.id
>=C2=A0 =C2=A0 =C2=A0<http://source.id>>
>=C2=A0 =C2=A0 =C2=A0 > WHEN MATCHED THEN
>=C2=A0 =C2=A0 =C2=A0 > UPDATE SET mid =C2=A0=3D source.mid
>=C2=A0 =C2=A0 =C2=A0 > WHEN NOT MATCHED THEN
>=C2=A0 =C2=A0 =C2=A0 > INSERT (id, mid, txn_timestamp, cre_ts)
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0 =C2=A0 VALUES (source.id &= lt;http://source.id> <http://source.id
>=C2=A0 =C2=A0 =C2=A0<http://source.id>>,source.mid,
>=C2=A0 =C2=A0 =C2=A0 >=C2=A0 =C2=A0source.txn_timestamp, source.cre_= ts);
>
>
>
> Actually , as per the business logic , we need to merge on a column > which is not unique or having any unique index on it. It's the lea= ding
> column of a composite unique key though. And in such scenarios=C2=A0th= e
> "INSERT.... ON CONFLICT" will give an error. So we are optin= g for a
> merge statement here, which will work fine with the column being
> having=C2=A0duplicate values in it.


Alright it's official I am confused.

You started with:

WITH source_data (id, mid, txn_timestamp, cre_ts) AS (
=C2=A0 =C2=A0 =C2=A0VALUES ('5efd4c91-ef93-4477-840c-a723ae212d66',= 123,
'2024-08-09T11:33:49.402585600Z', '2024-08-09T11:33:49.40258560= 0Z')
)
INSERT INTO tab1 (id, mid, txn_timestamp, cre_ts)
SELECT id, mid, txn_timestamp, cre_ts
FROM source_data
ON CONFLICT (id) DO UPDATE
SET=C2=A0 =C2=A0 mid =3D EXCLUDED.mid,
=C2=A0 =C2=A0 =C2=A0txn_timestamp =3D EXCLUDED.txn_timestamp,
=C2=A0 =C2=A0 =C2=A0cre_ts =3D EXCLUDED.cre_ts;

That implied that id was unique in of itself. As side note you called it a merge, which it is not as in MERGE. At this point I got off track
thinking of MERGE.

Then you went to the below which is a merge:

MERGE INTO tab1 AS target
USING (VALUES ('5efd4c91-ef93-4477-840c-a723ae212d99', 123,
'2024-08-09T11:33:49.402585600Z','2024-08-09T11:33:49.402585600= Z')) AS
source(id, mid,txn_timestamp, cre_ts)
ON target.id =3D source.id
WHEN MATCHED THEN
UPDATE SET mid=C2=A0 =3D source.mid
WHEN NOT MATCHED THEN
INSERT (id, mid, txn_timestamp, cre_ts)
=C2=A0 =C2=A0 =C2=A0VALUES (source.id,source.mid,=C2=A0 source.txn_ti= mestamp, source.cre_ts);

The question I have now is if id is part of a composite UNIQUE index on this:

=C2=A0 CREATE TABLE tab1 (
=C2=A0 =C2=A0 =C2=A0id varchar(100) ,
=C2=A0 =C2=A0 =C2=A0mid INT,
=C2=A0 =C2=A0 =C2=A0txn_timestamp TIMESTAMPTZ NOT NULL,
=C2=A0 =C2=A0 =C2=A0cre_ts TIMESTAMPTZ NOT NULL
) PARTITION BY RANGE (txn_timestamp);

Then what is the other column in the UNIQUE index?


--
Adrian Klaver
adrian.klaver@aklaver.com

--000000000000d94ea5061f5b1d52--