Postgres的数组唯一约束 [英] Postgres UNIQUE CONSTRAINT for array
本文介绍了Postgres的数组唯一约束的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何在所有的值像阵列中的唯一性创建一个约束:
How to create a constraint on the uniqueness of all the values in the array like:
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}视为相同,并在唯一约束检查
I need that would be the value {10, 22} and {22, 10} considered the same and check under the UNIQUE CONSTRAINT
推荐答案
我不认为你可以使用一个函数具有的唯一约束但你可以href=\"http://www.postgresql.org/docs/current/static/sql-createindex.html\">唯一一个首页的。因此,考虑一个排序功能是这样的:
I don't think you can use a function with a unique constraint but you can with a unique index. So given a sorting function something like this:
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;
然后,你可以这样做:
Then you could do this:
create table mytable (
interface integer[2]
);
create unique index mytable_uniq on mytable (sort_array(interface));
然后会发生以下情况:
Then the following happens:
=> 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
这篇关于Postgres的数组唯一约束的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文