I’m using Postgres v16. I’ve got a unique index on my invoice table:

CREATE UNIQUE INDEX unq_invoice_display_id 
ON invoice (tenant_id, display_id, expiry_timestamp) 
WHERE id = original_invoice_id;

# d unq_invoice_display_id
                Index "public.unq_invoice_display_id"
      Column      |          Type          | Key? |    Definition    
------------------+------------------------+------+------------------
 tenant_id        | bigint                 | yes  | tenant_id
 display_id       | character varying(255) | yes  | display_id
 expiry_timestamp | bigint                 | yes  | expiry_timestamp
unique, btree, for table "public.invoice", predicate (id = original_invoice_id)

Note the predicate on the index: predicate (id = original_invoice_id).

When I’m inserting a row I get a duplicate key error. However, the row I’m inserting does not have equal values in id and original_invoice_id.

insert into invoice (...,original_invoice_id,...,id) values (..,13484,...,13553);
ERROR:  duplicate key value violates unique constraint "unq_invoice_display_id"
DETAIL:  Key (tenant_id, display_id, expiry_timestamp)=(902, 758107, 253402214400000) already exists.

I know that the unq_invoice_display_id index causing the problem, because when I drop the index, the insert goes through.

Am I misunderstanding something? I don’t understand how inserting a row that does not match the predicate can cause a duplicate key error on the index.