Postgresql enforce unique two-way combination of columns
I'm trying to create a table that would enforce a unique combination of two columns of the same type - in both directions. E.g. this would be illegal:
col1 col2
1 2
2 1
I have come up with this, but it doesn't work:
database=> \d+ friend;
Table "public.friend"
Column | Type | Modifiers | Storage | Stats target | Description
--------------+--------------------------+-----------+----------+--------------+-------------
user_id_from | text | not null | extended | |
user_id_to | text | not null | extended | |
status | text | not null | extended | |
sent | timestamp with time zone | not null | plain | |
updated | timestamp with time zone | | plain | |
Indexes:
"friend_pkey" PRIMARY KEY, btree (user_id_from, user_id_to)
"friend_user_id_to_user_id_from_key" UNIQUE CONSTRAINT, btree (user_id_to, user_id_from)
Foreign-key constraints:
"friend_status_fkey" FOREIGN KEY (status) REFERENCES friend_status(name)
"friend_user_id_from_fkey" FOREIGN KEY (user_id_from) REFERENCES user_account(login)
"friend_user_id_to_fkey" FOREIGN KEY (user_id_to) REFERENCES user_account(login)
Has OIDs: no
Is it possible to write this without triggers or any advanced magic, using constraints only?
Solution 1:
A variation on Neil's solution which doesn't need an extension is:
create table friendz (
from_id int,
to_id int
);
create unique index ifriendz on friendz(greatest(from_id,to_id), least(from_id,to_id));
Neil's solution lets you use an arbitrary number of columns though.
We're both relying on using expressions to build the index which is documented https://www.postgresql.org/docs/current/indexes-expressional.html
Solution 2:
Do you consider the intarray extension to be magic?
You'd need to use int keys for the users instead of text though...
Here's a possible solution:
create extension intarray;
create table friendz (
from_id int,
to_id int
);
create unique index on friendz ( sort( array[from_id, to_id ] ) );
insert into friendz values (1,2); -- good
insert into friendz values (2,1); -- bad
http://sqlfiddle.com/#!15/c84b7/1