Feedback-ID: i76984098:Fastmail
Message-ID: <accc0da1-6be2-4a02-a2c3-fbe45f8da8d8@aklaver.com>
Date: Sun, 11 Aug 2024 08:11:49 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Insert works but fails for merge
To: Alban Hertroys <haramrae@gmail.com>, yudhi s <learnerdatabase99@gmail.com>
Cc: David G Johnston <david.g.johnston@gmail.com>,
 pgsql-general <pgsql-general@lists.postgresql.org>
References: <CAEzWdqc=QKpfFU3jNSoBrZ37y727GK2w2rSQ2qJCEGJDT5Pm6Q@mail.gmail.com>
 <37e09717-f121-4192-b152-18df17713414@aklaver.com>
 <CAEzWdqeW+yVwb2v8Vy-t9DC69_cMhUWTQqVBoL7_9qDzhBe8=Q@mail.gmail.com>
 <9dc702ca-8bb3-442e-bd2b-12abe81a84d0@aklaver.com>
 <CAEzWdqfLi-JtgZPhArq-6hAcSe9RnjN62LbeS8rVVSAiQOiOtg@mail.gmail.com>
 <A0C7725A-C82D-4B62-A485-D1C4CE7C5A8E@gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <A0C7725A-C82D-4B62-A485-D1C4CE7C5A8E@gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/accc0da1-6be2-4a02-a2c3-fbe45f8da8d8%40aklaver.com>
Precedence: bulk

On 8/11/24 03:09, Alban Hertroys wrote:
> 
>> On 10 Aug 2024, at 22:23, yudhi s <learnerdatabase99@gmail.com> wrote:
>> On Sat, Aug 10, 2024 at 8:22 PM Adrian Klaver <adrian.klaver@aklaver.com> 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.

The next sentence says:

"In other words, a target row shouldn't join to more than one data 
source row."

In this case the OP's data source is a single VALUES(). As it is written 
I don't it tripping that rule, though it would not take much to change that.


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

-- 
Adrian Klaver
adrian.klaver@aklaver.com