数组的 Postgres 唯一约束 [英] Postgres UNIQUE CONSTRAINT for array

查看:37
本文介绍了数组的 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} 视为相同并在 UNIQUE CONSTRAINT 下检查

I need that would be the value {10, 22} and {22, 10} considered the same and check under the UNIQUE CONSTRAINT

推荐答案

我不认为你可以使用带有 唯一约束 但您可以使用 独特的索引.所以给定一个排序函数是这样的:

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(anyarray) returns anyarray as $$
    select array_agg(distinct n order by n) from unnest($1) as t(n);
$$ 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

这篇关于数组的 Postgres 唯一约束的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆