Overview of the ALL() Construct in PostgreSQL

PostgreSQL has an ALL() construct that we can use when searching for data inside arrays. It returns a Boolean result, which reflects whether the condition is true or not.

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 ALL() construct against the c3 column for a particular expression:

SELECT 
    c3, 
    'Zebra' <> ALL(c3) AS "True/False"
FROM t1;

Result:

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

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 f (False), otherwise it’s t (True).

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

For example:

SELECT c1, c2, c3 FROM t1 
WHERE 'Zebra' <> ALL(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}

Here we returned just those rows where the condition is false (i.e. the rows that don’t contain Zebra).

We can negate the expression with the NOT operator:

SELECT c1, c2, c3 FROM t1 
WHERE NOT 'Zebra' <> ALL(c3);

Result:

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

This time we got all rows where the condition is true (i.e. all rows that contain Zebra).

Note that this is different to simply replacing <> with =:

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

Result:

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

If you find yourself trying to do this, maybe the ANY() construct is what you’re looking for:

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’s an example against the c4 column:

SELECT c1, c2, c4 FROM t1 
WHERE 10 > ALL(c4);

Result:

 c1 |   c2    |            c4             
----+---------+---------------------------
  1 | Animals | {{1,2,3},{4,5,6},{7,8,9}}
  3 | Pets    | {{8,9,7},{4,5,3},{9,8,9}}

The c4 column contains arrays of integers, and so our query looked for rows where all of the values were less than ten.

Passing a `NULL` Value

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

SELECT 'Zebra' = ALL(null);

Result:

NULL

Passing a Non Array

Passing a non array results in an error:

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

Result:

ERROR:  op ANY/ALL (array) requires array on right side
LINE 1: SELECT 'Zebra' = ALL(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.

Example

Passing a NULL Value

Passing a Non Array

Passing a `NULL` Value