1

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.

LogiTech
  • 47
  • 5
  • I don't think there is a way to do that, unless you use a trigger (which is not a perfect solution, as it is subject to race conditions). – Laurenz Albe Nov 04 '20 at 07:10
  • 1
    @NikolaiDmitriev What an unhelpful comment! – Laurenz Albe Nov 04 '20 at 07:11
  • 1
    I would be surprised if Postgres - or any other database - would support this. This kind of “repeating group” is generally avoided as poor design (not least because of issues such as your question); these repeating columns are better implemented as a new table (and you can immediately see how easy it is to enforce the desired uniqueness constraints on that) – racraman Nov 04 '20 at 07:12
  • @racraman: actually Postgres does have nearly everything in place to create such an [exclusion constraint](https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION) - however it doesn't support the `&&` operator for text/varchar values. If `path1` and `path2` were integers, it would be possible to define such a constraint –  Nov 04 '20 at 07:35

1 Answers1

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