We can use the && operator in PostgreSQL to check two arrays for any overlapping elements.

We include an array on each side of the operator to compare them, and the result is a Boolean value that indicates whether or not there’s any overlap. A result of True (or t) indicates that there’s an overlap, while False (or f) indicates there’s no overlap.

Example

Here’s a simple example to demonstrate:

SELECT ARRAY[ 3, 7, 9 ] && ARRAY[ 2, 6, 7 ];

Result:

t

This query returns t (true) because there is an overlap between the two arrays. Both arrays include 7 as an element, which means there’s an overlap.

Here’s what happens where there’s no overlap:

SELECT ARRAY[ 'Cat', 'Dog' ] && ARRAY[ 'Horse', 'Buffalo' ];

Result:

f

We get f for false as expected.

A Database Example

Suppose we have a table with the following data:

SELECT * FROM t1;

Result:

 c1 |     c2     |              c3               
----+------------+-------------------------------
  1 | {1,2,3}    | {{1,2,3},{4,5,6}}
  2 | {4,5,6}    | {{7,8,9},{10,11,12}}
  3 | {7,8,9}    | {{7,13,14},{15,16,17}}
  4 | {10,11,12} | {{100,200,300},{400,500,600}}

We can check each row for overlaps between the arrays in the c2 and c3 columns:

SELECT 
    c2,
    c3,
    c2 && c3 AS "True/False"
FROM t1;

Result:

     c2     |              c3               | True/False 
------------+-------------------------------+------------
 {1,2,3}    | {{1,2,3},{4,5,6}}             | t
 {4,5,6}    | {{7,8,9},{10,11,12}}          | f
 {7,8,9}    | {{7,13,14},{15,16,17}}        | t
 {10,11,12} | {{100,200,300},{400,500,600}} | f

We can use a WHERE clause to filter the result to just those rows that have an overlap:

SELECT * FROM t1
WHERE c2 && c3;

Result:

 c1 |   c2    |           c3           
----+---------+------------------------
  1 | {1,2,3} | {{1,2,3},{4,5,6}}
  3 | {7,8,9} | {{7,13,14},{15,16,17}}

Negate the Operator

We can negate the condition with the NOT operator:

SELECT * FROM t1
WHERE NOT c2 && c3;

Result:

 c1 |     c2     |              c3               
----+------------+-------------------------------
  2 | {4,5,6}    | {{7,8,9},{10,11,12}}
  4 | {10,11,12} | {{100,200,300},{400,500,600}}

Compare a Column to an Array Constant

We can also compare a column to an array constant:

SELECT * FROM t1
WHERE c2 && '{5}';

Result:

 c1 |   c2    |          c3          
----+---------+----------------------
  2 | {4,5,6} | {{7,8,9},{10,11,12}}

Another way to build the array is with the ARRAY constructor:

SELECT * FROM t1
WHERE c2 && ARRAY[5];

Result:

 c1 |   c2    |          c3          
----+---------+----------------------
  2 | {4,5,6} | {{7,8,9},{10,11,12}}

Non Arrays

If either of the expressions are non arrays we get an error:

SELECT * FROM t1
WHERE c1 && c3;

Result:

ERROR:  operator does not exist: integer && integer[]
LINE 2: WHERE c1 && c3;
                 ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.

In this case I replaced c2 with c1, but c1 is not an array column and so I got the error.

NULL Expressions

If any of the expressions resolve to NULL, then NULL is returned:

SELECT ARRAY[ 3, 7, 9 ] && NULL;

Result:

NULL

Doing this with the database query results in an empty result set:

SELECT * FROM t1
WHERE NULL && c3;

Result:

 c1 | c2 | c3 
----+----+----
(0 rows)