PostgreSQL 9.4 - 在EXCLUDE约束中使用自定义运算符

Question

在审查完这个答案之后，我创建了以下自定义运算符：

CREATE OR REPLACE FUNCTION is_not_distinct_from(
    ANYELEMENT, 
    ANYELEMENT
)
RETURNS BOOLEAN AS
$$
SELECT $1 IS NOT DISTINCT FROM $2;
$$
LANGUAGE sql 
IMMUTABLE;

CREATE OPERATOR =!= (
    PROCEDURE = is_not_distinct_from(anyelement,anyelement),
    LEFTARG  = anyelement,
    RIGHTARG = anyelement,
    COMMUTATOR = =!=,
    NEGATOR = <!>
);

CREATE OR REPLACE FUNCTION is_distinct_from(
    ANYELEMENT, 
    ANYELEMENT
)
RETURNS BOOLEAN AS
$$
SELECT $1 IS DISTINCT FROM $2;
$$
LANGUAGE sql 
IMMUTABLE;

CREATE OPERATOR <!> (
    PROCEDURE = is_distinct_from(anyelement,anyelement),
    LEFTARG  = anyelement,
    RIGHTARG = anyelement,
    COMMUTATOR = <!>,
    NEGATOR = =!=
);

我有一个表格，允许两个NULL值，并且我想防止重复记录，将NULL视为一个值。

CREATE TABLE "Foo" (
    "FooID" SERIAL PRIMARY KEY,
    "Foo" TEXT NOT NULL,
    "Bar" TIMESTAMPTZ
    "Baz" TIMESTAMPTZ
    EXCLUDE ("Foo" WITH =, "Bar" WITH =!=, "Baz" WITH =!=)
);

我遇到了以下错误：

ERROR: operator =!=(anyelement,anyelement)不是“datetime_ops”运算符族的成员 DETAIL: 排除运算符必须与约束的索引运算符类相关联。

我已经查看了文档（此处和此处），但我很难理解材料。

此外，这个问题可能被认为是重复的问题；然而，那个问题的问题是与其他RDBMS兼容性的问题... 这个问题具体讨论如何处理上述错误。

- losthorse

---

opclass文档没有这个链接和这个链接就没什么用了。btree_gist扩展的timestamp的GiST实现在这里和这里，这只是为了让你了解一下你要做的事情。 - Nick Barnes

就我所知，排除约束似乎不是必要的；我认为这样的约束（理论上）应该可以通过唯一索引和自定义B树操作类来实现。但是我尝试过，它似乎不起作用。我怀疑空值的处理方式已经硬编码到索引方法中了。不确定GiST操作类是否会有所不同。 - Nick Barnes

Answer 1

您选择了一个真正的考验。使用唯一索引会更加简单、安全和快速。

CREATE TABLE foo (
    foo_id serial PRIMARY KEY,
    foo text NOT NULL,
    bar timestamptz,
    baz timestamptz
);
CREATE TABLE

CREATE UNIQUE INDEX foo_foo_bar_baz_idx ON foo 
(foo, coalesce(bar, 'infinity'), coalesce(baz, 'infinity'));
CREATE INDEX

INSERT INTO foo VALUES
(default, '', null, null),
(default, '', now(), null),
(default, '', null, now());
INSERT 0 3

INSERT INTO foo VALUES
(default, '', null, null);
ERROR:  duplicate key value violates unique constraint "foo_foo_bar_baz_idx"
DETAIL:  Key (foo, (COALESCE(bar, 'infinity'::timestamp with time zone)), (COALESCE(baz, 'infinity'::timestamp with time zone)))=(, infinity, infinity) already exists.

- klin

---

我试图避免在函数索引中使用coalesce()，而是坚持使用EXCLUDE约束；然而，您在回答中说它更简单、更安全、更快，但没有提供更多信息...您能否解释一下所请求的方法存在的问题？ - losthorse

Exclude 创建一个唯一索引。那么，如果可以使用一个语句获得类似的结果，为什么要定义自己的运算符、运算符类和索引类型呢？我认为没有理由避免使用 coalesce()，因为 IS DISTINCT 实际上几乎等同于它。为什么更安全？代码越少，麻烦就越少。 - klin

@losthorse 此外，现有功能的测试要比自定义代码更加严格。 - jpmc26

我同意你的推理（代码少，麻烦少），@jpmc26 也提出了一个有说服力的观点... 我同意你们两个，coalesce() 在这种情况下可能是更好的选择；然而，你的回答没有回答问题... 如果你能扩展你的答案来包括如何将自定义运算符与索引绑定（可能举个例子），那么我会接受它。 - losthorse

你应该为给定的数据和索引类型创建运算符类。但是，你必须非常小心，不要覆盖现有的默认运算符类，除非你定义了索引实现中预期的所有运算符和函数。这不是一项微不足道的任务，你应该有非常重要的理由去尝试处理它。 - klin