Postgresql Exclusion constraint with soft-deleted rows

Question

I am trying to accomplish a PostgreSQL constraint on te following table:

CREATE TABLE contracts
(
    id bigint NOT NULL,
    startdate date NOT NULL,
    enddate date NOT NULL,
    price numeric(19,2) NOT NULL,
    deleted boolean NOT NULL,
    supplier_id bigint NOT NULL,
)

It contains contracts for suppliers with different prices. For a given time only one contract can exists for a given supplier. I have made the following constraint to enforce that:

ALTER TABLE contracts ADD CONSTRAINT overlaping_contracts EXCLUDE USING GIST (
        supplier_id WITH =,
        daterange(startdate, enddate) WITH &&
);

This ensures that one cannot insert a new contract that overlaps with an already existing contract. Now we also support "soft-deletion" of contracts. This invalidates the old contract and sets the "deleted" flag to true. Now I want to insert a new contract for the same period, but this overlaps triggering the constraint.

I tried to combine a conditional unqiue index with the above constaint but cannot make it work. The documentation on exclusion constraints is quite small.

My instinct tells me I have to add something like

deleted = false

to the exclusion constraint, but I can't find the right syntax to do so.

How can I combine an exclusion constraint with a conditional unique index so I can enforce the overlapping constraint only on rows which have deleted = false?

Answer 1

This should do the trick:

    ALTER TABLE contracts ADD CONSTRAINT overlapping_contracts EXCLUDE USING GIST (
    supplier_id WITH =,
    daterange(startdate, enddate) WITH &&
    ) WHERE (NOT deleted);

Using this gist https://gist.github.com/fphilipe/0a2a3d50a9f3834683bf