Unique constraint on two columns irrespective of order

Question

I have a pretty simple table:

    id  | name   | alternate
--------+--------+------------
     1  |   Joe  |     Joseph
--------+--------+------------
     2  |   Pete |     Peter
--------+--------+------------

and so on.

I want to add a constraint on the name and alternate column, but irrespective of order. For example, I don't want to be able to insert (3, 'Peter', 'Pete') as that is essentially the same as id 2, just with the columns reversed.

Is there a way to do this?

Answer 1

Create a unique index:

CREATE UNIQUE INDEX ON atable
   (LEAST(name, alternate), GREATEST(name, alternate));

Answer 2

You can create a unique index:

create unique index my_unique_idx
on mytable(least(name, alternate), greatest(name, alternate));

Demo on DB Fiddle:

create table mytable (name varchar(10), alternate varchar(10));

create unique index my_unique_idx
on mytable(least(name, alternate), greatest(name, alternate));

insert into mytable values('foo', 'bar');
-- 1 rows affected

insert into mytable values('bar', 'foo')
-- ERROR:  duplicate key value violates unique constraint "my_unique_idx"
-- DETAIL:  Key (LEAST(name, alternate), GREATEST(name, alternate))=(bar, foo) already exists.