2

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.

rept
  • 2,086
  • 1
  • 26
  • 44

1 Answers1

3

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])