On 11/11/24 07:40, yudhi s wrote:
We have a merge query as below for a partition table which is range
partitioned on a truncated date column 'part_date'. And the only
unique key in this table is a composite primary key on (id,
part_date). And this merge queries ON condition is based on one of the
columns i.e ID which is the leading column of the PK.So it means it
will ensure no duplication happens for ID values, but still we are
seeing "duplicate key" error. So what is the possible reason here or
are we encountering any buggy behaviour here?
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.
Regards
Yudhi
