PostgreSQL在多列上实现唯一值

Question

我有一个PostgreSQL表

id    ColA    ColB
------------------
1     'a'     'b'
2     'c'     'd'

我希望让ColA和ColB中的值在两列中都是唯一的，即任何这样的插入操作都是被禁止的：

INSERT INTO table (ColA,ColB) values('a','e');
INSERT INTO table (ColA,ColB) values('z','a');
INSERT INTO table (ColA,ColB) values('d','g');

任何这些插入均可：

INSERT INTO table (ColA,ColB) values('z','e');
INSERT INTO table (ColA,ColB) values('l','k');

所以

CONSTRAINT unique_name UNIQUE (ColA,ColB)

不适合，因为它将允许任何前4个插入。

- Anton

Answer 1

很遗憾，这不能通过简单的唯一约束/索引轻松解决（如果可以解决的话）。

你需要的是一种排除 约束：即能够基于类似碰撞的东西来排除某些行。唯一约束只是特定的排除约束（它们基于等式碰撞）。

因此，理论上，你只需要排除每个row1，其中已经有一个row2，对于该表达式为真：ARRAY[row1.cola，row1.colb] && ARRAY[row2.cola，row2.colb]

这个索引可能可以胜任（目前只有gist索引支持排除约束）：

ALTER TABLE table_name
  ADD CONSTRAINT table_name_exclusion
  EXCLUDE USING gist ((ARRAY[cola, colb]) WITH &&);

但是，遗憾的是，没有针对数组（使用gist）的默认运算符类。有一个intarray模块，它仅为integer数组提供了一个运算符类，但没有为text数组提供任何内容。

如果你真的想解决这个问题，你可以总是滥用range类型（例如，我使用了相邻的-|-运算符，处理了所有无法使用unique处理的情况）...

-- there is no built-in type for text ranges neither,
-- but it can can be created fairly easily:
CREATE TYPE textrange AS RANGE (
  SUBTYPE = text
);

ALTER TABLE table_name
  ADD CONSTRAINT table_name_exclusion
  EXCLUDE USING gist ((textrange(least(cola, colb), greatest(cola, colb))) WITH -|-);

-- the exclusion constraint above does not handle all situations:

ALTER TABLE table_name
  ADD CONSTRAINT table_name_check
  CHECK (cola is distinct from colb); -- without this, empty ranges could be created,
                                      -- which are not adjacent to any other range

CREATE UNIQUE INDEX table_name_unique
  ON table_name ((ARRAY[least(cola, colb), greatest(cola, colb)]));
     -- without this, duplicated rows could be created,
     -- because ranges are not adjacent to themselves

...但我担心，通过进行一些数据库重构，您的原始问题可以更轻松地解决；这就引出了一个问题：您想用这个解决什么问题？

- pozs

---

我有一个类似的问题：用户表有两个用于电子邮件地址的列。一个用于常规电子邮件，另一个用于新的未验证电子邮件。现在我希望它们无论是否已经验证都是唯一的。 - Tigerware