How to find duplicates in postgres arrays?

Question

PostgreSQL

There is a column in postgre that stores an array of integers.
For example:
{1,2,3}
{4, 5}
{3, 6}
You need to find these lines one and three where at least one element is repeated. Please tell me how to implement it?

Answer 1

If it can be decomposed to the 3rd normal form of the database, then it is better to do it, otherwise you will have to use a crutch of 4 functions to get to the values ​​​​in these arrays and somehow work with them later in queries.

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

Solution B (initial data - array type).

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

Answer 2

Relational algebra practically does not know how to operate with horizontal collections. That is how she is. And SQL was created for other things.
The best thing to do is to create a temporary table. Merge your arrays there with a 90-degree rotation and execute the simplest (! really simple!) query with grouping.
This decision will be ideologically more correct than loading SQL with tasks that are not characteristic of it.