CODEWITHSUNDEEP
postgresql
gli
gli

Reputation: 338

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

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:

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

Upvotes: 5

Views: 787

Answers (1)

Stas Kelvich
Stas Kelvich

Reputation: 86

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

Upvotes: 7

Related Questions