public inbox for [email protected]
help / color / mirror / Atom feedFrom: 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 01:53:11 +0530
Message-ID: <CAEzWdqfLi-JtgZPhArq-6hAcSe9RnjN62LbeS8rVVSAiQOiOtg@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]>
On Sat, Aug 10, 2024 at 8:22 PM Adrian Klaver <[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; = 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>,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.
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: <CAEzWdqfLi-JtgZPhArq-6hAcSe9RnjN62LbeS8rVVSAiQOiOtg@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