public inbox for [email protected]  

help / color / mirror / Atom feed

From: yudhi s <[email protected]>
To: Achilleas Mantzios - cloud <[email protected]>
Cc: [email protected]
Subject: Re: Duplicate key error
Date: Tue, 12 Nov 2024 00:20:07 +0530
Message-ID: <CAEzWdqcMnG8FuSVA_4OKPKwL0CC-LiOHeUx4sYSpScazvSBTXQ@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CAEzWdqeKafRTLDSP-0Ffw8nijQPUXKpgaHxMXoqz0yEdad-2aA@mail.gmail.com>
	<[email protected]>

On Mon, Nov 11, 2024 at 1:57 PM Achilleas Mantzios - cloud <
[email protected]> wrote:

>
> > WITH source_data (col1, col2, col3.....col29) AS (VALUES ($1,
> > $2::date, $3::timestamptz, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13,
> > $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26,
> > $27::timestamptz, $28, $29::timestamptz)) MERGE INTO cpod.TAB1 AS
> > target USING source_data AS source ON target.ID = source.ID WHEN
> > MATCHED THEN UPDATE SET ....) WHEN NOT MATCHED THEN INSERT (...)
> > VALUES (....);
> >
> > Error:
> > ERROR: duplicate key value violates unique constraint "TAB1_pkey"
> > Detail: Key (ID, part_date)=(XXXXXXXXX, 2024-11-04) already exists.
>
> You have to use the whole composite unique key (including part_date)
> when matching against source_data .
>
> If you had uniqueness on "ID" and then added "part_name" to the key,
> you'd still had uniqueness.
>
> BUT, reversely, if you have uniqueness on the pair (ID, part_date) there
> is no guaranteed uniqueness on ID alone, hence your ERROR.
>
>
>
Actually the table is partitioned on column part_date which is why the
unique key is composite i.e. on ID and part_date. So in that case even we
merge on one of the column i.e. ID which is unique in itself, if any
duplicate value ID tries to be merged it should satisfy the MATCH criteria
and thus it should do a UPDATE on table TAB1 rather than going for NOMATCH
or INSERT. So in that case it should not error out with "duplicate key
violation". Is my understanding correct here?

view thread (6+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Duplicate key error
  In-Reply-To: <CAEzWdqcMnG8FuSVA_4OKPKwL0CC-LiOHeUx4sYSpScazvSBTXQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox