Postgresql - How to check if arrays have elements in common

Question

I have two columns with array type and I would like to check if they have at least one element in common. How can I do it?

For example:

ARRAY[1,4,3] | ARRAY[2,1]

Both arrays contains the number 1, so it should return the row.

Yago Azedias · Accepted Answer · 2019-07-25 17:49:17Z

8

Use the && operator

As described at postgresql official documentation you can use the following command to check if the arrays have one intersection value:

SELECT * FROM mytable m WHERE m.array1 && m.array2

Which, of course, m.array1 and m.array2 are both array types

user330315

answered Jul 25, 2019 at 17:47

Yago Azedias

4,9584 gold badges20 silver badges32 bronze badges

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

1 Answer 1