postgresql unique constraint for any integer from two columns (or from array)

Question

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);

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:

• 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?

Answer 1

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}).