Postgresql enforce unique two-way combination of columns
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
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); -- goodinsert into friendz values (2,1); -- bad