14

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?

tadeas
  • 189
  • 1
  • 6

2 Answers2

26

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

Gary - Stand with Ukraine
  • 7,081
  • 1
  • 30
  • 39
  • 2
    Nice one dude. Very simple – Neil McGuigan May 06 '15 at 05:21
  • 1
    Thank you. Could not find this anywhere. – parreirat Jun 16 '16 at 17:47
  • How can I make this work with an upsert? The ON CONFLICT (col1, col2) form does not seems to work since this is an index. – Votagus Jan 26 '21 at 16:28
  • For the ON CONFLICT DO NOTHING case, you don't need to specify anything. For the ON CONFLICT DO UPDATE case, I think you don't need to give a column list (col1, col2) but you can specify the index name directly if I remember correctly. – Flo Jan 03 '22 at 13:19
5

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

Neil McGuigan
  • 46,580
  • 12
  • 123
  • 152