Как получить уникальные значения из 2 столбцов в PostgreSQL

Question

У меня есть столбец "id_a" и столбец "id_b".

"id_a" - это целочисленный [] тип.

"id_b" - это тип int.

Мне нужно найти способ проверить, что больше нет значений id_a с тем же id_b

For Example:
id_a {3,4,5}    id_b 18

Другие примеры:

ERROR:
id_a{3,4,5}  id_b 18 --> because i have already the value of b with the same values of a 

NO ERROR:
id_a{3,4,5}  id_b 19

ANOTHER ERROR:
id_a{3}      id_b 19

NO ERROR:
id_a{6}      id_b 18

Answer 1

You can create an exclusion constraint if you install the btree_gist extension:

create table data (id_a int[], id_b int);

alter table data
  add constraint check_ids 
  exclude using gist (id_a with &&, id_b with =);

Initial row - no problem

insert into data (id_a, id_b) values ('{3,4,5}', 18);

Выполнение вышеуказанного снова приводит к ошибке, как и ожидалось.

Следующие работы:

insert into data (id_a, id_b) values ('{3,4,5}', 19);

А потом:

insert into data (id_a, id_b) values ('{3}', 19);

приведет к ошибке

ОШИБКА: конфликтующее значение ключа нарушает ограничение исключения "check_ids"

Пример онлайн

Answer 2

Вы можете использовать ограничение исключения, например:

      create table mytable (
    id_a int[],
    id_b int,
    exclude using gist(id_a with &&, id_b with =)
);

Это предотвращает перекрытие массивов на id_aдля данного id_b.

Демонстрация скрипта БД:

      insert into mytable values ('{3,4,5}', 18);
-- 1 rows affected

insert into mytable values ('{3,4,5}', 18);
-- ERROR:  conflicting key value violates exclusion constraint "mytable_id_a_id_b_excl"
-- DETAIL:  Key (id_a, id_b)=({3,4,5}, 18) conflicts with existing key (id_a, id_b)=({3,4,5}, 18).

insert into mytable values ('{3,4,5}', 19);
-- 1 rows affected

insert into mytable values ('{3}', 19);
-- ERROR:  conflicting key value violates exclusion constraint "mytable_id_a_id_b_excl"
-- DETAIL:  Key (id_a, id_b)=({3}, 19) conflicts with existing key (id_a, id_b)=({3,4,5}, 19).

insert into mytable values ('{6}', 18);
-- 1 rows affected