Как найти дубли в массивах postgres?

Question

[vhsporno]

Есть столбец в постгре, который хранит в себе массив интежеров.
Например:
{1,2,3}
{4, 5}
{3, 6}
Нужно найти вот эти строку один и три где хотя бы один элемент повторяется. Подскажите пожалуйста как это реализовать?

Answer 1

[alexalexes]

Если это можно декомпозировать до 3-ей нормальной формы базы данных, то лучше сделать это, иначе придется использовать костыль из 4 функций, чтобы выйти на значения в этих массивах и хоть как-то работать потом с ними в запросах.

select a.* from
(
  select b.id, b.split_value, count(b.id) over (partition by b.split_value) as v_count
  from
  (
    select c.id, cast(unnest(string_to_array(translate(c.value,'{}',''),',')) as INTEGER) as split_value
    from
    ( -- имитация данных таблицы
      (select 0 as id, '{1,2,3}' as value) union all
      (select 1, '{4, 5}') union all
      (select 2, '{3, 6}') 
    ) c
  ) b
) a
where a.v_count > 1

Решение Б (исходные данные - тип массив).

select a.* from
(
  select b.id, b.split_value, count(b.id) over (partition by b.split_value) as v_count
  from
  (
    select c.id, unnest(c.value) as split_value
    from
    ( -- имитация данных таблицы
      (select 0 as id, array[1,2,3] as value) union all
      (select 1, array[4,5]) union all
      (select 2, array[3,6]) 
    ) c
  ) b
) a
where a.v_count > 1

Comments

[vhsporno]

select c.id, cast(unnest(string_to_array(translate(c.qualification_documents,'{}',''),',')) as INTEGER) as split_value
    from
    ( -- имитация данных таблицы
      select id, qualification_documents from communication_sailorkeys
    ) c

Говорит такую ошибку

ERROR: function translate(integer[], unknown, unknown) does not exist Подсказка: No function matches the given name and argument types. You might need to add explicit type casts.

[alexalexes]

Вместо:
translate(c.qualification_documents,'{}','')
Используйте:

replace(replace(c.qualification_documents,'{',''),'}','')

Эта функция нужна, чтобы обрезать начальные и конечные фигурные скобки.
Дано: '{1,2,3}'
Для этой строки порядок действий такой:
1. translate - убираем скобки -> '1,2,3'
2. string_to_array - преобразуем строку в тип данных массив -> [1,2,3]
3. unnest - разворачиваем массив на несколько строк -> первая строка: 1, вторая: 2 ...
4. cast - в каждой строке преобразуем значение колонки в целое число.
Все, теперь можно использовать в SQL.
PS: Если атрибут qualification_documents уже по типу данных является массивом, то шаги 1, 2 и 4 не нужны, куча вложенных функций заметно уменьшится.
Скорее всего, в вашем случае достаточно использовать unnest (добавил решение Б).

Answer 2

[mayton2019]

Реляционная алгебра практически не умеет оперировать горизонтальными коллекциями. Такова она есть. И SQL создавался для других дел.

Самое правильное что можно сделать - создать временную табличку. Слить туда твои массивы с разворотом в 90 градусов и выполнить простейший (! реально простейший!) запрос с группировкой.

Это решение будет идеологически правильней, чем нагружать SQL не-свойственными ему задачами.