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?

A unique index / constraint isn't going to be enough here, but you might be able to do something with an exclusion constraint. I've seen them described as a kind of "super generalised unique constraint", but never actually used one, and the manual seems rather light on examples. — IMSoP
– IMSoP, Commented Dec 2, 2016 at 14:52

Stas Kelvich · Accepted Answer · 2016-12-02 15:09:58Z

7

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

answered Dec 2, 2016 at 15:09

Stas Kelvich

861 silver badge2 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user330315 Over a year ago

You don't need to store that as an array in the table. EXCLUDE USING gist ((array[a,b]) WITH &&) will work as well

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related