PostgreSQL独特的价值创造了多列 [英] PostgreSQL unique value acreoss multiple columns

问题描述

我有PostgreSQL表

I have PostgreSQL table

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

我想使ColA和ColB中的值在两列中都是唯一的，即，禁止任何这些插入：

I want to make values in ColA and ColB to be unique across both columns i.e. any of these inserts would be forbidden:

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

，并且允许使用以下任何插入内容：

and any of these inserts would be allowed:

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

所以

CONSTRAINT unique_name UNIQUE (ColA,ColB)

不适用，因为它将允许任何

is not suited, because it will allow any of previous 4 inserts.

推荐答案

可悲的是，使用简单的唯一约束/索引（如果可以使用

Sadly, this cannot be solved easily with simple unique contraints / indexes (if it can be solved with them at all).

您需要的是排除 约束：基于类似 collision 的东西排除某些行的能力。唯一约束只是特定的排除约束（它们基于相等性冲突）。

What you need, is an exclusion constraint: the ability to exclude some rows, based on something like collision. Unique constraints are just specific exclusion constraints (they are based on equality collisions).

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

So, in theory, you just need to exclude every row1, where there is already a row2, for which this expression is true: ARRAY[row1.cola, row1.colb] && ARRAY[row2.cola, row2.colb]

该索引可以完成工作（目前仅 gist 索引支持排除约束）：

This index could do the job (currently only gist indexes support exclusion constraints):

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

但是不幸的是，数组没有默认的运算符类（使用要点）。有一个 intarray 模块，仅为整数数组提供一个，而为 text 数组不提供任何内容。

But unfortunately, there is no default operator class for arrays (which uses gist). There is an intarray module, which provides one for only integer arrays, but nothing for text arrays.

如果您确实想解决此问题，则可以随时滥用 范围类型（例如，我使用了相邻的-|-运算符，可以处理所有情况，用 unique ）无法处理...

If you really want to work this out, you can always abuse the range types (f.ex. I used the adjacent -|- operator, which handles all the cases, which cannot be handled with 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

...但是，恐怕您只需通过一点数据库重构就可以更轻松地解决您原来的问题；这使我们想到了一个问题：您想解决什么问题？

... but I'm afraid, your original problem could be solved much easier with a little database refactoring; which brings us to the question: what problem, do you want to solve with this?

这篇关于PostgreSQL独特的价值创造了多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！