Postgres overlap arrays from one column

Question

I have a Table "A" with one column "col1" where each record is a array of integers.

col1
-----
{1,2,3,4}
{1,2,6,7}
{1,2,3,8,9}

I like to have one row as result which contains the overlap or intersect of all arrays in "col1".

select overlap(col1) from A;

result
-----
{1,2}

If the number of items in each set is variable, then this is going to be an ugly problem. A better option would be to make your data relational. Instead of having a CSV list, have a one record for each value in the set in one column, and some sort of group identifier in another column. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 9, 2016 at 3:25

Pavel Stehule · Accepted Answer · 2016-06-09 04:46:51Z

8

You should to define custom aggregate for this purpose:

CREATE OR REPLACE FUNCTION public.overlap_array_aggregate(anyarray, anyarray)
 RETURNS anyarray
 LANGUAGE plpgsql STRICT
AS $function$
BEGIN
  RETURN ARRAY(SELECT unnest($1) INTERSECT SELECT unnest($2));
END;
$function$

CREATE AGGREGATE array_overlap_agg (
   basetype = anyarray,
   sfunc =  overlap_array_aggregate,
   stype = anyarray );

Then it is working as you expect:

postgres=# SELECT * FROM foo;
┌─────────────┐
│      a      │
╞═════════════╡
│ {1,2,3,4}   │
│ {1,2,6,7}   │
│ {1,2,3,8,9} │
└─────────────┘
(3 rows)

postgres=# SELECT array_overlap_agg(a) FROM foo;
┌───────────────────┐
│ array_overlap_agg │
╞═══════════════════╡
│ {1,2}             │
└───────────────────┘
(1 row)

edited Jun 9, 2016 at 4:46

answered Jun 9, 2016 at 4:15

Pavel Stehule

46.7k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ezequiel Tolnay Over a year ago

Excellent answer. A possible performance improvement (since INTERSECT operates at the row level), can be RETURN CASE WHEN $1 && $2 THEN ARRAY(SELECT unnest($1) INTERSECT SELECT unnest($2)) END; which only would perform the INTERSECT if there is at least one element in common via the && array operator.

Pavel Stehule Over a year ago

The effect of your proposal depends how often the intersection will be empty. When result will be empty array, then it can helps, when result will be non empty array, then it can decrease performance. But if you need really best performance, then you can write C extension. All depends on usage.

sten Over a year ago

how will you do overlap_count(ary, tbl_column) i.e. between array passed as argument and specific column ?

Pavel Stehule Over a year ago

@sten - can you send some example? Can you open new query?

sten Over a year ago

here : stackoverflow.com/questions/66626593/array-overlap-count

Collectives™ on Stack Overflow

Postgres overlap arrays from one column

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related