Feedback-ID: i76984098:Fastmail
Message-ID: <a301dd68-fd01-43b5-a22e-ac8f38855488@aklaver.com>
Date: Sat, 10 Aug 2024 14:27:37 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Insert works but fails for merge
To: 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>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAEzWdqfLi-JtgZPhArq-6hAcSe9RnjN62LbeS8rVVSAiQOiOtg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/a301dd68-fd01-43b5-a22e-ac8f38855488%40aklaver.com>
Precedence: bulk

On 8/10/24 13:23, yudhi s wrote:
> 
> 
> On Sat, Aug 10, 2024 at 8:22 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> 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
adrian.klaver@aklaver.com