Postgresql enforce unique two-way combination of columns

Question

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?

Gary - Stand with Ukraine · Accepted Answer · 2020-05-18 11:29:59Z

29

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

edited May 18, 2020 at 11:29

answered May 4, 2015 at 18:37

Gary - Stand with Ukraine

7,6291 gold badge33 silver badges41 bronze badges

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

5 Comments

Neil McGuigan Over a year ago

Nice one dude. Very simple

Votagus Over a year ago

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.

Flo Over a year ago

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.

2pac Over a year ago

Is there a way to do so with two columns of type text? Or would you need to go via the triggers approach?

Gary - Stand with Ukraine Over a year ago

@2pac - the least and greatest functions support character data types so will work fine.

Neil McGuigan · Accepted Answer · 2015-05-04 18:33:27Z

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

Collectives™ on Stack Overflow

Postgresql enforce unique two-way combination of columns

2 Answers 2

5 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Linked

Related