如何创建对数组中所有值的唯一性的约束,如：
- CREATE TABLE mytable
- (
- interface integer[2],CONSTRAINT link_check UNIQUE (sort(interface))
- )
我的排序功能
- create or replace function sort(anyarray)
- returns anyarray as $$
- select array(select $1[i] from generate_series(array_lower($1,1),array_upper($1,1)) g(i) order by 1)
- $$language sql strict immutable;
我需要这样的价值{10,22}和{22,10}被认为是一样的,并在独特的约束下检查
我不认为你可以使用一个
unique constraint的功能,但你可以使用
unique index.所以给出一个这样的排序功能：
- create function sort_array(integer[]) returns integer[] as $$
- select array_agg(n) from (select n from unnest($1) as t(n) order by n) as a;
- $$language sql immutable;
那么你可以这样做：
- create table mytable (
- interface integer[2]
- );
- create unique index mytable_uniq on mytable (sort_array(interface));
然后发生以下情况：
- => insert into mytable (interface) values (array[11,23]);
- INSERT 0 1
- => insert into mytable (interface) values (array[11,23]);
- ERROR: duplicate key value violates unique constraint "mytable_uniq"
- DETAIL: Key (sort_array(interface))=({11,23}) already exists.
- => insert into mytable (interface) values (array[23,11]);
- ERROR: duplicate key value violates unique constraint "mytable_uniq"
- DETAIL: Key (sort_array(interface))=({11,23}) already exists.
- => insert into mytable (interface) values (array[42,11]);
- INSERT 0 1
