From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | yudhi s <learnerdatabase99(at)gmail(dot)com> |
Cc: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, pgsql-general <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Insert works but fails for merge |
Date: | 2024-08-10 21:27:37 |
Message-ID: | a301dd68-fd01-43b5-a22e-ac8f38855488@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 8/10/24 13:23, yudhi s wrote:
>
>
> On Sat, Aug 10, 2024 at 8:22 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | yudhi s | 2024-08-10 21:40:52 | Re: Insert works but fails for merge |
Previous Message | Lok P | 2024-08-10 21:06:42 | Re: Column type modification in big tables |