Duplicate key error - Mailing list pgsql-general

From yudhi s
Subject Duplicate key error
Date
Msg-id CAEzWdqeKafRTLDSP-0Ffw8nijQPUXKpgaHxMXoqz0yEdad-2aA@mail.gmail.com
Whole thread Raw
Responses Re: Duplicate key error
Re: Duplicate key error
List pgsql-general
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.


Regards
Yudhi

pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Fwd: Fwd: Postgres attach partition: AccessExclusive lock set on different tables depending on how attaching is performed
Next
From: Kirill Reshke
Date:
Subject: CREATE SCHEMA ... CREATE M.V. support