Using the ANY() Construct in PostgreSQL

In PostgreSQL, we can use the ANY() construct to perform searches against arrays. It returns a Boolean result, and so we can incorporate this into our searches to return just those rows that yield true or false, as the case may be.

There’s also a SOME() construct, which is a synonym for ANY().

Example

Suppose we have the following table:

SELECT * FROM t1;

Result:

 c1 |     c2     |                          c3                           |                 c4                  
----+------------+-------------------------------------------------------+-------------------------------------
  1 | Animals    | {Zebra,Bird,Mouse,Cow,Pig}                            | {{1,2,3},{4,5,6},{7,8,9}}
  2 | Music      | {Rock,Jazz,Blues,Electronica,Metal}                   | {{32,78,14},{102,99,37},{18,65,29}}
  3 | Pets       | {Cat,Dog,Zebra,Buffalo}                               | {{8,9,7},{4,5,3},{9,8,9}}
  4 | Sports     | {Rugby,Hockey,Soccer,Golf,Cricket,Baseball,Wrestling} | {{1,2,1},{9,10,3},{7,8,5}}
  5 | Activities | {Golf,Walking,Running,Climbing}                       | {{4,32,56},{45,1,56},{37,5,9}}
(5 rows)

Here’s a query that shows the output of the ANY() construct against the c3 column for a particular expression:

SELECT 
    c3, 
    'Zebra' = ANY(c3) AS "True/False"
FROM t1;

Result:

                          c3                           | True/False 
-------------------------------------------------------+------------
 {Zebra,Bird,Mouse,Cow,Pig}                            | t
 {Rock,Jazz,Blues,Electronica,Metal}                   | f
 {Cat,Dog,Zebra,Buffalo}                               | t
 {Rugby,Hockey,Soccer,Golf,Cricket,Baseball,Wrestling} | f
 {Golf,Walking,Running,Climbing}                       | f

The True/False column shows the outcome of the expression. If the array in the c3 column contains a Zebra element, then the result is t (True), otherwise it’s f (False).

We can therefore filter our queries based on the output of such expressions.

For example:

SELECT c1, c2, c3 FROM t1 
WHERE 'Zebra' = ANY(c3);

Result:

 c1 |   c2    |             c3             
----+---------+----------------------------
  1 | Animals | {Zebra,Bird,Mouse,Cow,Pig}
  3 | Pets    | {Cat,Dog,Zebra,Buffalo}

Here we returned just those rows where the condition is true.

We can negate the expression with the NOT operator:

SELECT c1, c2, c3 FROM t1 
WHERE NOT 'Zebra' = ANY(c3);

Result:

 c1 |     c2     |                          c3                           
----+------------+-------------------------------------------------------
  2 | Music      | {Rock,Jazz,Blues,Electronica,Metal}
  4 | Sports     | {Rugby,Hockey,Soccer,Golf,Cricket,Baseball,Wrestling}
  5 | Activities | {Golf,Walking,Running,Climbing}

This time we got all rows where the condition is false.

Passing a NULL Value

If we pass a NULL value, then NULL is returned:

SELECT 'Zebra' = ANY(null);

Result:

NULL

Passing a Non Array

Passing a non array results in an error:

SELECT 'Zebra' = ANY(c1) FROM t1;

Result:

ERROR:  op ANY/ALL (array) requires array on right side
LINE 1: SELECT 'Zebra' = ANY(c1) FROM t1;
                       ^

In this case I passed column c1 which is an integer column (not an array column), and so an error was returned.

The SOME() Construct

As mentioned, SOME() is a synonym for ANY(). Therefore we can replace ANY() with SOME() in any of the previous examples, and we’d get the same result.

Here’s the first example again, but with a column added for SOME():

SELECT 
    c3, 
    'Zebra' = ANY(c3) AS "ANY()",
    'Zebra' = SOME(c3) AS "SOME()"
FROM t1;

Result:

                          c3                           | ANY() | SOME() 
-------------------------------------------------------+-------+--------
 {Zebra,Bird,Mouse,Cow,Pig}                            | t     | t
 {Rock,Jazz,Blues,Electronica,Metal}                   | f     | f
 {Cat,Dog,Zebra,Buffalo}                               | t     | t
 {Rugby,Hockey,Soccer,Golf,Cricket,Baseball,Wrestling} | f     | f
 {Golf,Walking,Running,Climbing}                       | f     | f

Both ANY() and SOME() return the same result.