Apology for the confusion. The other column is the txn_timestamp in the composite unique key, which is also the partition key.
But yes we cant use both in the ON clause because of certain business requirements. We realized it late. And that's why "on conflict " We are unable to use.
On Sun, 11 Aug, 2024, 2:57 am Adrian Klaver, <adrian.klaver@xxxxxxxxxxx> wrote:
On 8/10/24 13:23, yudhi s wrote:
>
>
> On Sat, Aug 10, 2024 at 8:22 PM Adrian Klaver <adrian.klaver@xxxxxxxxxxx
> <mailto:adrian.klaver@xxxxxxxxxxx>> 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@xxxxxxxxxxx
