How to Use && for Array Overlap in PostgreSQL

In PostgreSQL, the && operator checks if two arrays have any elements in common, returning true if they overlap and false otherwise. Use it in a WHERE clause to filter rows where arrays share at least one element.

Syntax

The && operator is used between two arrays to test if they overlap. It returns true if there is at least one common element, otherwise false.

• array1 && array2: Checks if array1 and array2 share any elements.

sql

array1 && array2

Example

This example shows how to use && to find rows where the tags array overlaps with a given array.

sql

CREATE TABLE items (
  id SERIAL PRIMARY KEY,
  name TEXT,
  tags TEXT[]
);

INSERT INTO items (name, tags) VALUES
('Apple', ARRAY['fruit', 'red', 'sweet']),
('Carrot', ARRAY['vegetable', 'orange', 'crunchy']),
('Strawberry', ARRAY['fruit', 'red', 'small']),
('Broccoli', ARRAY['vegetable', 'green', 'healthy']);

-- Find items with tags overlapping ['red', 'sweet']
SELECT id, name, tags
FROM items
WHERE tags && ARRAY['red', 'sweet'];

Output

 id |    name    |           tags           
----+------------+--------------------------
  1 | Apple      | {fruit,red,sweet}
  3 | Strawberry | {fruit,red,small}

Common Pitfalls

Common mistakes when using && include:

• Using it with non-array types, which causes errors.
• Confusing && with logical AND; && here is specifically for array overlap.
• Not using ARRAY[] syntax for literal arrays in queries.

sql

/* Wrong: Using && with non-array types */
-- SELECT 'abc' && 'b'; -- ERROR

/* Correct: Use arrays */
SELECT ARRAY['a','b','c'] && ARRAY['b','d']; -- Returns true

Output

 ?column? 
----------
 t
(1 row)

Quick Reference

Operator	Description	Example	Result
&&	Checks if two arrays overlap	ARRAY[1,2,3] && ARRAY[3,4,5]	true
&&	No overlap between arrays	ARRAY['a','b'] && ARRAY['c','d']	false

Key Takeaways

The && operator checks if two arrays share any elements and returns true if they do.

Use ARRAY[] syntax to define arrays when using && in queries.

&& only works with array types, not with scalar or string types directly.

It is useful for filtering rows based on overlapping array values.