public inbox for [email protected]  

help / color / mirror / Atom feed

From: Alban Hertroys <[email protected]>
To: yudhi s <[email protected]>
Cc: 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 12:09:44 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAEzWdqfLi-JtgZPhArq-6hAcSe9RnjN62LbeS8rVVSAiQOiOtg@mail.gmail.com>
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>


> On 10 Aug 2024, at 22:23, yudhi s <[email protected]> wrote:
> On Sat, Aug 10, 2024 at 8:22 PM Adrian Klaver <[email protected]> wrote:
> > 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.

Then how is the database supposed to determine which of those duplicate rows it should update? In the best case, it would update all of the duplicates with the same values, which usually is not what you want.

> It's the leading column of a composite unique key though.

Which could be unique of itself, I suppose that isn’t the case here?

In that case, IMHO your best course of action is to do something about those duplicates first.

> 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.

I’m not so sure about that claim…

At least on MSSQL, MERGE has this requirement: "A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times.”. I’ve seen that as an error message on occasion.

The MERGE documentation for PostgreSQL says this: "You should ensure that the join produces at most one candidate change row for each target row.”, which also seems to imply that you shouldn’t have duplicates.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

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], [email protected]
  Subject: Re: Insert works but fails for merge
  In-Reply-To: <[email protected]>

* 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