Searching Data in Arrays: ANY, ALL, array_contains()

Arrays are often used to store multiple values in a single column—like tags, numbers, roles, or preferences. But how do you check if an array contains a value you need, or if all elements match a certain condition? PostgreSQL gives you handy tools for these checks: ANY, ALL, and array_contains(). Let’s break each one down.

What is ANY?

Imagine you have an array listing several values, like product IDs, phone numbers, or a list of hobbies. You’ll often need to check if a certain value is in the array. That’s where the ANY operator comes in. It lets you check if your “target object” is in the array.

Syntax for ANY

SELECT *
FROM your_table
WHERE value = ANY(array_column);

Here:

• array_column — this is the array you’re checking.
• value — this is the value you’re looking for in the array.

Example of using ANY

Let’s say we have a students table with the following structure:

We’re interested in students who have swimming among their hobbies. The query would look like this:

SELECT *
FROM students
WHERE 'swimming' = ANY(hobbies);

Result:

In short, ANY checks: “Is this value in the array?”

Trust, but verify everything! Using ALL

Now imagine the opposite: you need to make sure that all elements of the array match a certain rule. For that, PostgreSQL has the ALL operator. With it, you can check if all elements of the array, for example, are equal to a value, greater than a certain number, or match any other logical condition.

Syntax for ALL

SELECT *
FROM your_table
WHERE value < ALL(array_column);

Here:

• array_column — this is the array you’re checking.
• value < ALL(...) means that all elements of the array must be greater than the value.

Let’s go back to our students table. Suppose we want to find students whose hobbies all start with the letter “r”. Query:

SELECT *
FROM students
WHERE 'reading' = ALL(hobbies);

The result will be empty, because nobody has all hobbies equal to “reading”. To make the example more clear (and working), let’s think about numeric data.

Suppose there’s an orders table:

Find all orders where every item is cheaper than 400:

SELECT *
FROM orders
WHERE 400 > ALL(prices);

Result:

Filtering rows by array contents

We’ve covered the basic functions and operators for searching data in arrays, but how do you use them in practice? Let’s look at a few usage examples.

Example 1: Find students who have at least one hobby that’s music

Original table:

SELECT *
FROM students
WHERE 'music' = ANY(hobbies);

Result:

Example 2: Find orders where all prices are more than 100

Original orders table:

SELECT *
FROM orders
WHERE 100 < ALL(prices);

Result:

Common mistakes and how to avoid them

When you’re working with arrays, especially searching for data, there are a few gotchas you might run into.

Mistake: Incorrect use of ANY or ALL. For example, if you accidentally mix up the operators, you can get unexpected results.

-- Wrong way:
SELECT *
FROM students
WHERE hobbies = ANY('reading');

In this example, the mistake is that hobbies is an array, and 'reading' is a string. It’s the array that should be checked with ANY, not the other way around.

Mistake: Array indexing starts at 1, not 0. If you use arrays in other functions or try to pull out elements manually, keep in mind how PostgreSQL handles this.

Mistake: No indexing. If you use arrays for frequent search operations (ANY, ALL), adding indexes can really speed up your queries.