PostgreSQL ANY operator

This article describes how to use the ANY operator check if at least one value in a set of values satisfies the specified condition.

Sometimes, you need to check if there is at least one value in a list that satisfies a specified condition, for example:

  • Checks whether a value list contains a specified value.
  • Checks if a list has an element greater or less than a specified value.

You can use the PostgreSQL ANY operator to solve these problems.

The PostgreSQL ANY operator is used to compare a value with a list of values ​​and return true as long as a value in the list of values ​​satisfies the specified condition, otherwise it returns false.

In PostgreSQL, the SOME operator is a synonym for ANY. You can use the SOME keyword instead ANY.

PostgreSQL ANY syntax

To check if there is at least one value in an array that satisfies a condition, use the ANY operator like this:

comparison_operator ANY (array)

Here:

  • The comparison_operator is a comparison operator such as =, !=, >, >=, <, <=etc.
  • The array following the ALL operator is an array or subquery. Subqueries must be enclosed in parentheses.
  • The expression returns true if at least one value in array satisfies the specified condition, otherwise the expression returns false.

For examples:

  • value = ANY (array): This expression returns true if any value in array equals value, otherwise it returns false.
  • value > ANY (array): This expression returnstrue as any value in array is less than value, otherwise it returns false.
  • value < ANY (array): This expression returnstrue as any value in array is greater than value, otherwise it returns false.
  • value <> ANY (array): This expression returns true as any value in array is not equal to value, otherwise it returns false.

PostgreSQL ANY Examples

PostgreSQL ANY and arrays

We often need to check whether an array contains at least one element that satisfies a condition, such as:

  • To check if [1, 2, 3] contains an element with the value 2, use the following statement:

    SELECT 2 = ANY(ARRAY[1, 2, 3]);

    ?column?
----------
t

  • To check if [1, 2, 3] contains an element with a value greater than 2, use the following statement:

    SELECT 2 < ANY(ARRAY[1, 2, 3]);

    ?column?
----------
t

  • To check if [1, 2, 3] contains an element with a value greater than 3, use the following statement:

    SELECT 3 < ANY(ARRAY[1, 2, 3]);

    ?column?
----------
f

  • To check if [1, 2, 3] contains an element whose value is not equal to 3, use the following statement:

    SELECT 3 <> ANY(ARRAY[1, 2, 3]);

    ?column?
----------
t

PostgreSQL ANY and subqueries

Here we provide some practical examples to demonstrate the power of the ANY operator.

Let’s use the film table from the PostgreSQL Sakila sample database .

  • To if there is any film with rentals higher than $5, use the following ANY statement:

    SELECT 5 < ANY (SELECT rental_rate FROM film);

    ?column?
----------
f

    Here, notice the subqueries:

    SELECT rental_rate FROM film

    It returns all rentals of all films.

    Eventually, the expression returns false, which means that there are no films in the film table with rentals greater than $5.

  • To check if a film can be rented for $1, use the following statement:

    SELECT 1 >= ANY (SELECT rental_rate FROM film);

    ?column?
----------
t

    To check whether a film can be rented for 1 dollar, it is to check whether there is a rent less than or equal to 1 dollar.

    The expression returns true, which means there are films in the film table with rents less than or equal to $1. That is, you can rent a film for $1.

Conclusion

PostgreSQL ANY operator is used to compare a value with a list of values ​​and return true if any value in the list ​​satisfies the specified condition, otherwise it returns false. The ANY operator can be used in the WHERE clause to filter rows with specified conditions.