How do I specify unique constraint for 2 columns that should be treated as one in PostgreSQL?

Question

Let's say I have a table:

    table people (
    user,
    path1,
    path2,
);

I would like to make a constrain that it restrict adding/updating if there is already a record with the same name either in column path1 or path2.

Example:

INSERT INTO people (user, path1, path2)
VALUES ('George', 'New York', 'Toronto',);

INSERT INTO people (user, path1, path2)
VALUES ('Joe', 'Toronto', 'LA',);

On the second insert, there should be an error because 'Toronto' is already defined in the path2 of 1st recording.

Answer 1

In theory a exclusion constraint would be a way to solve this.

However, the following code is not working:

create table people 
(
  "user" text,
  path1 text,
  path2 text
);

alter table people 
  add constraint check_path
  exclude using gist ("user" with =, (array[path1, path2]) with && );

The above results in the error:

ERROR: data type text[] has no default operator class for access method "gist"

---

But: the && operator works with integer arrays. So if the "path" columns can be turned into an integer e.g. a foreign key referencing a lookup table, this could be achieved with the following:

create table location
(
  id integer primary key, 
  name varchar(50) not null unique
);

create table people 
(
  "user" text,
  path1 int not null references location,
  path2 int not null references location
);

alter table people 
  add constraint check_path
  exclude using gist ("user" with =, (array[path1, path2]) with && );
  
insert into location (id, name)
values 
(1, 'New York'),
(2, 'Toronto'),
(3, 'LA');

Then this insert will apparently work:

insert into people ("user", path1, path2)
values 
  ('George', 1, 2);

But this will result in an error:

insert into people ("user", path1, path2)
values ('George', 2, 3);

The error will be:

ERROR: conflicting key value violates exclusion constraint "check_path"
DETAIL: Key ("user", (ARRAY[path1, path2]))=(George, {2,3}) conflicts with existing key ("user", (ARRAY[path1, path2]))=(George, {1,2}).

Online example