postgresql - 是否可以在排除约束中使用 IS NOT DISTINCT FROM？

Question

我有下表:

CREATE TABLE claim (
    claim_number TEXT NOT NULL,
    line_id TEXT,
    process TEXT NOT NULL
);

我想给它添加一个约束，使 claim_number 和 line_id 的组合始终是唯一的。

在大多数情况下，line_id 为空，并且该 claim 编号只有一行。在某些情况下，给定的 claim_number 会有多行，在这些情况下，line_id 将始终包含一个值。这里的目标是能够在 (claim_number, line_id) 组合上有一个强制唯一组合的约束，这样我就可以将它用作 INSERT...ON CONFLICT DO UPDATE 中的冲突目标> 语句，以便可以更新进程列。 UNIQUE 约束不起作用，因为它不计算 NULL = NULL，这有道理，但不是我需要的。

我尝试添加排除约束，例如:

ALTER TABLE claim
ADD EXCLUDE ( claim_number WITH =,
              line_id WITH IS NOT DISTINCT FROM);

但是失败了:

ERROR:  syntax error at or near "IS"

有没有办法在排除约束中使用 IS NOT DISTINCT FROM？

Answer 1

在 MSSQL Server 中，这是使用过滤唯一索引完成的，其中过滤谓词是它只索引具有非 NULL 值的行。

我不是 PostgreSQL 专家，但谷歌搜索显示使用“部分索引”是可能的:https://www.postgresql.org/docs/current/static/indexes-partial.html

CREATE UNIQUE INDEX ix_claim_lines ON claim ( claim_number, line_id )
    WHERE line_id IS NOT NULL