来自两列(或来自数组)的任何整数的 postgresql 唯一约束 [英] postgresql unique constraint for any integer from two columns (or from array)

问题描述

如何保证两列/数组中任意整数的唯一性?

How to guarantee a uniqueness of any integer from the two columns / array?

示例:我创建一个表并在其中插入一行:

Example: I create a table and insert one row in it:

CREATE TABLE mytest(a integer NOT NULL, b integer NOT NULL);
INSERT INTO mytest values (1,2);

我应该创建什么 UNIQUE INDEX 以不允许添加以下任何值

What UNIQUE INDEX should I create to not allow add any of the following values

INSERT INTO mytest values (1,3); # because 1 is already there
INSERT INTO mytest values (3,1); # because 1 is already there
INSERT INTO mytest values (2,3); # because 2 is already there
INSERT INTO mytest values (3,2); # because 2 is already there

如果有帮助，我可以有两个元素的数组而不是两列.

I can have array of two elements instead of two columns if it helps somehow.

当然，我可以发明一些解决方法，以下是我想到的:

Surely, I can invent some workaround, the following come into my mind:

• 为所有数字创建单独的表，在那里有唯一索引，并通过事务将值添加到两个表中.如果号码不唯一，则不会添加到第二张表中，交易失败
• 添加两行而不是一行，并为 id-of-the-pair 添加额外的字段.

• create separate table for all numbers, have unique index there, and add values to two tables with transaction. If the number is not unique, it won't be added to the second table, and transaction fails
• add two rows instead of one, and have additional field for id-of-the-pair.

但我想要一张桌子，我需要一排有两个元素.这可能吗?

But I want to have one table and I need one row with two elements in it. Is that possible?

推荐答案

你可以在 table 上使用排除约束和 intarray 来快速执行重叠数组的搜索:

You can use exclusion constraint on table along with intarray to quickly perform search of overlapping arrays:

CREATE EXTENSION intarray;
CREATE TABLE test (
    a int[],
    EXCLUDE USING gist (a gist__int_ops WITH &&)
);

INSERT INTO test values('{1,2}');

INSERT INTO test values('{2,3}');
>> ERROR:  conflicting key value violates exclusion constraint "test_a_excl"
>> DETAIL:  Key (a)=({2,3}) conflicts with existing key (a)=({1,2}).

这篇关于来自两列(或来自数组)的任何整数的 postgresql 唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！