On Mon, Nov 11, 2024 at 1:57 PM Achilleas Mantzios - cloud <a.mantzios@xxxxxxxxxxxxxxxxxxxx> 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?
