据我理解,您想要在 a
和 b
组合上创建一个 UNIQUE
索引。
您缩小了更新范围(b
不应存在于 a
中)。这个解决方案更为严格。
解决方案
经过尝试和调查(见下文!),我得出了以下结论:
ALTER TABLE tbl ADD CONSTRAINT a_not_equal_b CHECK (a <> b);
ALTER TABLE tbl ADD CONSTRAINT ab_unique
EXCLUDE USING gist ((ARRAY[hashtext(COALESCE(a, ''))
, hashtext(COALESCE(b, ''))]) gist__int_ops WITH &&);
db<>fiddle 這裡
由於排除約束目前(pg 12)無法與text[]
一起使用,因此我們使用哈希值的int4[]
。 hashtext()
是內置的哈希函數,也用於哈希分區(等等其他用途)。 看起來非常適合這個工作。
操作類gist__int_ops
由附加模塊intarray提供,每個數據庫需要安裝一次。它是可選的,解決方案也可以使用默認的數組操作類。 只需刪除gist__int_ops
即可回退。 但是intarray更快。 相關:
注意事项
int4
可能不足以充分排除哈希冲突。您可能需要改用 bigint
。但这更昂贵,无法使用 gist__int_ops
操作符类来提高性能。由您决定。
Unicode 具有令人沮丧的特性,即相等的字符串可以以不同的方式编码。如果您使用 Unicode(典型编码为 UTF8)并使用非 ASCII 字符(并且这对您很重要),请比较规范化形式以排除此类重复项。即将推出的 Postgres 13 添加了函数 normalize()
用于此目的。然而,这是字符类型重复的一般警告,而不是特定于我的解决方案。
NULL
值是允许的,但与空字符串 (''
) 冲突。我宁愿使用 NOT NULL
列,并从表达式中删除 COALESCE()
。
到达排除约束的障碍赛道
我的第一反应是:排除约束。但它不起作用:
ALTER TABLE tbl ADD CONSTRAINT ab_unique EXCLUDE USING gist ((ARRAY[a,b]) WITH &&);
ERROR: data type text[] has no default operator class for access method "gist"
HINT: You must specify an operator class for the index or define a default operator class for the data type.
这个问题有一个待完成项。相关内容:
但我们不能为 text[]
使用GIN索引吗?遗憾的是,不行:
ALTER TABLE tbl ADD CONSTRAINT ab_unique EXCLUDE USING gin ((ARRAY[a,b]) WITH &&);
ERROR: access method "gin" does not support exclusion constraints
为什么?
手册:。
访问方法必须支持 amgettuple
(参见第61章);目前这意味着不能使用GIN。
看起来实现起来很困难,所以别抱太大希望。
如果 a
和 b
是 integer
列,我们可以使用整数数组来实现它的工作:
ALTER TABLE tbl ADD CONSTRAINT ab_unique EXCLUDE USING gist ((ARRAY[a,b]) WITH &&);
或者使用附加模块 intarray 中的 gist__int_ops
运算符类(通常更快):
ALTER TABLE tbl ADD CONSTRAINT ab_unique EXCLUDE USING gist ((ARRAY[a,b]) gist__int_ops WITH &&);
若想在同一行内禁止重复,可以添加一个 CHECK
约束:
ALTER TABLE tbl ADD CONSTRAINT a_not_equal_b CHECK (a <> b);
未解决的问题:如何处理NULL
值。
解决方法
添加一个辅助表,将a
和b
的值存储在一个列中:
CREATE TABLE tbl_ab(ab text PRIMARY KEY);
主表,就像你原来的那样,加上外键约束。
CREATE TABLE tbl (
a text REFERENCES tbl_ab ON UPDATE CASCADE ON DELETE CASCADE
, b text REFERENCES tbl_ab ON UPDATE CASCADE ON DELETE CASCADE
);
使用如下函数来进行INSERT操作:
INSERT
。
CREATE OR REPLACE FUNCTION f_tbl_insert(_a text, _b text)
RETURNS void
LANGUAGE sql AS
$func$
WITH ins_ab AS (
INSERT INTO tbl_ab(ab)
SELECT _a WHERE _a IS NOT NULL
UNION ALL
SELECT _b WHERE _b IS NOT NULL
)
INSERT INTO tbl(a,b)
VALUES (_a, _b);
$func$;
db<>fiddle 这里
或者在后台实现一个触发器来处理它。
CREATE OR REPLACE FUNCTION trg_tbl_insbef()
RETURNS trigger AS
$func$
BEGIN
INSERT INTO tbl_ab(ab)
SELECT NEW.a WHERE NEW.a IS NOT NULL
UNION ALL
SELECT NEW.b WHERE NEW.b IS NOT NULL;
RETURN NEW;
END
$func$ LANGUAGE plpgsql;
CREATE TRIGGER tbl_insbef
BEFORE INSERT ON tbl
FOR EACH ROW EXECUTE PROCEDURE trg_tbl_insbef();
db<>fiddle 这里
NULL
处理可以根据需要更改。
无论哪种方式,虽然添加了(可选的)FK约束强制执行我们不能规避助手表tbl_ab
,并允许在tbl_ab
中进行 UPDATE
和 DELETE
级联,但仍然需要将 UPDATE
和DELETE
投影到辅助表中(或实现更多触发器)。棘手的边角情况,但有解决方案。在找到上述使用hashtext()
的排除约束的解决方案后,不再深入探讨...
相关: