在Postgres数据库中,如果有以下三列:第一列,第二列和第三列。如何创建一个约束条件,使排列组合唯一?
例如,如果('foo','bar','shiz')
已经存在于数据库中,则('bar','shiz','foo')
将被排除为非唯一。
在Postgres数据库中,如果有以下三列:第一列,第二列和第三列。如何创建一个约束条件,使排列组合唯一?
例如,如果('foo','bar','shiz')
已经存在于数据库中,则('bar','shiz','foo')
将被排除为非唯一。
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)
);
同时需要占用最少的磁盘空间:
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()
进行测量。我的例子只使用单个字符,大小差别是恒定的。
您可以通过创建一个唯一索引来实现,该索引基于返回列值排序数组的函数:
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]));
同事建议,变化自 @julien 的想法:
按字母顺序排序术语,并在每个术语的两侧放置分隔符。将它们连接起来并放置在单独的字段中,该字段成为主键。
为什么要使用分隔符?以便可以插入“a”、“aa”、“aaa”和“aa”、“aa”、“aa”。