Count the number of overlapping elements in Postgres array

Question

I have an array of integer as a column.

Is there a way to query the number of overlapping integers?

For example for the following 3 records:

COLUMN1
{1,3,7}
{3,4,5}
{1,2}

How can I get the number of overlapping elements with ARRAY[3,4,8] ?

The result for my example should be:

1 (element 3)
2 (element 3 and 4)
0 (none)

I've found

SELECT COLUMN1 && ARRAY[44,45,46] FROM table1

But that returns a true or false.

user330315 · Accepted Answer · 2018-05-08 10:46:03Z

4

If you install the intarray extension you can use the "intersection" operator from that extension:

select column1, column1 & ARRAY[3,4,8] as elements
from table1

returns:

column1 | elements
--------+---------
{1,3,8} | {3,8}   
{3,4,5} | {3,4}   
{1,2}   | {}

To get the number of the elements in the resulting array, use cardinality(column1 & ARRAY[3,4,8])

answered May 8, 2018 at 10:46

user330315

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

1 Comment

100% what I was looking for! Thx.