跨多个列的排列的唯一约束条件

Question

在Postgres数据库中，如果有以下三列：第一列，第二列和第三列。如何创建一个约束条件，使排列组合唯一？

例如，如果('foo'，'bar'，'shiz')已经存在于数据库中，则('bar'，'shiz'，'foo')将被排除为非唯一。

- KevDog

---

我也想知道这个。只是出于好奇。 - ffflabs

你的列可以为空吗？它们可以是空的吗？请定义“唯一”。你使用的是哪个Postgres版本？我在你的问题中没有看到用于测试的表定义。 - Erwin Brandstetter

非空。9.2.2 的值都是文本。 - KevDog

你可能想升级到9.2.4版本，以消除在该版本中修补的安全漏洞。更多详情请参见http://cve.mitre.org/cgi-bin/cvename.cgi?name=CVE-2013-1899。 - bma

Answer 1

您可以使用hstore创建唯一索引：

CREATE UNIQUE INDEX hidx ON test USING BTREE (hstore(ARRAY[a,b,c], ARRAY[a,b,c]));

Fiddle

UPDATE

Actually

CREATE UNIQUE INDEX hidx ON test USING BTREE (hstore(ARRAY[a,b,c], ARRAY[null,null,null]));

可能更好的想法是使用相同的功能，但占用更少的空间（fiddle）。

- Jakub Kania

Answer 2

仅使用基本表达式的这个唯一索引，对于仅有三列的情况，应该能够良好地执行。不需要额外的模块，如hstore或自定义函数：

CREATE UNIQUE INDEX t_abc_uni_idx ON t (
  LEAST(a,b,c)
, GREATEST(LEAST(a,b), LEAST(b,c), LEAST(a,c))
, GREATEST(a,b,c)
);

SQL fiddle

同时需要占用最少的磁盘空间：

SELECT pg_column_size(row(hstore(t))) AS hst_row
      ,pg_column_size(row(hstore(ARRAY[a,b,c], ARRAY[a,b,c]))) AS hst1
      ,pg_column_size(row(hstore(ARRAY[a,b,c], ARRAY[null,null,null]))) AS hst2
      ,pg_column_size(row(ARRAY[a,b,c])) AS arr
      ,pg_column_size(row(LEAST(a,b,c)
                        , GREATEST(LEAST(a,b), LEAST(b,c), LEAST(a,c))
                        , GREATEST(a,b,c))) AS columns
FROM t;

 hst_row | hst1 | hst2 | arr | columns
---------+------+------+-----+---------
      59 |   59 |   56 |  69 |      30

在这个例子中，数字是索引行的字节大小，使用pg_column_size()进行测量。我的例子只使用单个字符，大小差别是恒定的。

- Erwin Brandstetter

---

巧妙地使用GREATEST和LEAST。 - bma

Answer 3

您可以通过创建一个唯一索引来实现，该索引基于返回列值排序数组的函数：

CREATE OR REPLACE FUNCTION sorted_array(anyarray)
RETURNS anyarray
AS $BODY$
  SELECT array_agg(x) FROM (SELECT unnest($1) AS x FROM test ORDER BY x) AS y;
$BODY$
LANGUAGE sql IMMUTABLE;

CREATE UNIQUE index ON test (sorted_array(array[first,second,third]));

- qqx

---

也许值得检查一下手动排序的几个嵌套CASE表达式的性能影响。只有3列，应该是可行的。不过可能不值得额外的时间。 - Richard Huxton

Richard，性能不是这里的驱动因素。这是一个小应用程序，每天可能只有20-100个插入操作。 - KevDog

Answer 4

同事建议，变化自 @julien 的想法：

按字母顺序排序术语，并在每个术语的两侧放置分隔符。将它们连接起来并放置在单独的字段中，该字段成为主键。

为什么要使用分隔符？以便可以插入“a”、“aa”、“aaa”和“aa”、“aa”、“aa”。

- KevDog