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.