带有比较谓词的 PostgreSQL 排除约束

Question

有简单的模仿唯一约束，如

create table foo(x int, exclude (x with =));

但是怎么可能使用 IS NOT DISTINCT FROM 而不是 = (所以表中只有一个 NULL 值) ？

创建像 f(anyelement, anyelement) 和运算符这样的函数失败，因为 null 具有未知类型。那么还有一个问题:是否可以将 IS NOT DISTINCT FROM 谓词包装到 PostgreSQL 中的运算符中？

请耐心等待，我不是在寻找替代解决方案，我知道很多 :)

进一步阅读:Comparison operators

Answer 1

很容易创建对应于NOT DISTINCT TO的函数和运算符:

CREATE FUNCTION ndist(anyelement, anyelement) RETURNS boolean
   IMMUTABLE CALLED ON NULL INPUT LANGUAGE sql
   AS 'SELECT $1 IS NOT DISTINCT FROM $2';

CREATE OPERATOR === (
   PROCEDURE = ndist,
   LEFTARG = anyelement,
   RIGHTARG = anyelement,
   COMMUTATOR = "==="
);

如果两个参数都是未类型化的 NULL，这将失败:

test=> SELECT NULL === NULL;
ERROR:  could not determine polymorphic type because input has type unknown

一种解决方案是使用重载并为您需要的每种类型定义相同的函数和运算符:

CREATE FUNCTION ndist(integer, integer) RETURNS boolean
   IMMUTABLE CALLED ON NULL INPUT LANGUAGE sql
   AS 'SELECT $1 IS NOT DISTINCT FROM $2';

CREATE FUNCTION ndist(text, text) RETURNS boolean
   IMMUTABLE CALLED ON NULL INPUT LANGUAGE sql
   AS 'SELECT $1 IS NOT DISTINCT FROM $2';

CREATE OPERATOR === (
   PROCEDURE = ndist,
   LEFTARG = integer,
   RIGHTARG = integer,
   COMMUTATOR = "==="
);

CREATE OPERATOR === (
   PROCEDURE = ndist,
   LEFTARG = text,
   RIGHTARG = text,
   COMMUTATOR = "==="
);

那么这个例子就可以运行了:

test=> SELECT NULL === NULL;
 ?column? 
----------
 t
(1 row)

这是因为在这种情况下，类型解析规则将优先选择 text 上的运算符。

但是所有这些都不允许您创建排除约束，因为您的运算符未与运算符类相关联，这对于确定要使用哪种索引是必需的。

您必须为每个 btree 创建一个匹配函数 index method strategies并使用这些函数为 btree 定义一个运算符类。