Re: Insert works but fails for merge - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: Insert works but fails for merge |
Date | |
Msg-id | a301dd68-fd01-43b5-a22e-ac8f38855488@aklaver.com Whole thread Raw |
In response to | Re: Insert works but fails for merge (yudhi s <learnerdatabase99@gmail.com>) |
Responses |
Re: Insert works but fails for merge
|
List | pgsql-general |
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
pgsql-general by date: