Re: Duplicate key error

On Mon, Nov 11, 2024 at 1:57 PM Achilleas Mantzios - cloud <
a(dot)mantzios(at)cloud(dot)gatewaynet(dot)com> 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?