public inbox for [email protected]  

help / color / mirror / Atom feed

From: yudhi s <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: David G. Johnston <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: Insert works but fails for merge
Date: Sun, 11 Aug 2024 03:10:52 +0530
Message-ID: <CAEzWdqcHthQef8aAine9dUCrH-A+__xunt273KrwA7hv1LGQTA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAEzWdqc=QKpfFU3jNSoBrZ37y727GK2w2rSQ2qJCEGJDT5Pm6Q@mail.gmail.com>
	<[email protected]>
	<CAEzWdqeW+yVwb2v8Vy-t9DC69_cMhUWTQqVBoL7_9qDzhBe8=Q@mail.gmail.com>
	<[email protected]>
	<CAEzWdqfLi-JtgZPhArq-6hAcSe9RnjN62LbeS8rVVSAiQOiOtg@mail.gmail.com>
	<[email protected]>

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, <[email protected]>
wrote:

> On 8/10/24 13:23, yudhi s wrote:
> >
> >
> > On Sat, Aug 10, 2024 at 8:22 PM Adrian Klaver <[email protected]
> > <mailto:[email protected]>> 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 <http://target.id; <http://target.id
> >     <http://target.id>; = source.id <http://source.id; <http://source.id
> >     <http://source.id>;
> >      > WHEN MATCHED THEN
> >      > UPDATE SET mid  = source.mid
> >      > WHEN NOT MATCHED THEN
> >      > INSERT (id, mid, txn_timestamp, cre_ts)
> >      >      VALUES (source.id <http://source.id; <http://source.id
> >     <http://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 = EXCLUDED.mid,
>      txn_timestamp = EXCLUDED.txn_timestamp,
>      cre_ts = 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 = source.id
> WHEN MATCHED THEN
> UPDATE SET mid  = 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
> [email protected]
>
>

view thread (8+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Insert works but fails for merge
  In-Reply-To: <CAEzWdqcHthQef8aAine9dUCrH-A+__xunt273KrwA7hv1LGQTA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox