PostgreSQL排除约束条件与比较谓词

Question

PostgreSQL排除约束条件与比较谓词

3

可以简单地模拟唯一约束条件，例如

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

但是如何使用IS NOT DISTINCT FROM替代=(这样表中只有一个NULL值)呢？创建像f(anyelement, anyelement)和操作符一样的函数失败，因为null具有未知类型。那么又有一个问题：在PostgreSQL中是否可以将IS NOT DISTINCT FROM谓词包装成操作符呢？请耐心等待，我不会寻求替代解决方案，我知道这很多 :) 此外阅读：比较运算符。

- Abelisto

@Ryan 当然。但是我在寻求通用解决方案。 - Abelisto

2

你是指这个吗？https://dev59.com/Wm865IYBdhLWcg3wduaH#7630564 - Erwin Brandstetter

一种不是很舒适的解决方案是使用CREATE OPERATOR定义自己的运算符。 - clemens

1

@Abelisto：这有什么关系呢？当您将其用作表约束时，输入不会具有类型“unknown”... - Nick Barnes

1

@Abelisto：哦，那么你可能在考虑这个讨论rCTE解决方案的链接：https://dev59.com/mV8e5IYBdhLWcg3wo7mE#25536748；附带一个指向这个维基页面的链接：https://wiki.postgresql.org/wiki/Loose_indexscan（最新更新也是我做的）。关键词是“松散索引扫描”。 - Erwin Brandstetter

显示剩余9条评论

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Laurenz Albe · Accepted Answer

很容易创建一个与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 索引方法策略 创建匹配函数，并针对这些函数定义一个btree的操作类。