sql - 具有软删除行的 Postgresql 排除约束

Question

我正在尝试对下表完成 PostgreSQL 约束:

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,
)

它包含不同价格的供应商的契约(Contract)。对于给定时间，给定供应商只能存在一个契约(Contract)。我已经制定了以下约束来强制执行:

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

这可确保无法插入与现有契约(Contract)重叠的新契约(Contract)。现在我们也支持合约的“软删除”。这会使旧契约(Contract)无效并将“已删除”标志设置为真。现在我想插入一份同一时期的新契约(Contract)，但这重叠触发了约束。

我尝试将条件唯一索引与上述约束结合使用，但无法使其工作。关于排除约束的文档非常少。

我的直觉告诉我必须添加类似的东西

deleted = false

到排除约束，但我找不到正确的语法。

如何将排除约束与条件唯一索引结合起来，以便我可以仅对已删除 = false 的行强制执行重叠约束？

Answer 1

这应该可以解决问题:

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

使用这个要点 https://gist.github.com/fphilipe/0a2a3d50a9f3834683bf