How to Use ALL with Array in PostgreSQL: Syntax and Examples

In PostgreSQL, you can use the ALL keyword with arrays to compare a value against every element of the array. The syntax is value operator ALL (array), which returns true only if the comparison holds for all elements in the array.

Syntax

The basic syntax to use ALL with an array in PostgreSQL is:

• value operator ALL (array)

Here:

• value is the single value you want to compare.
• operator is a comparison operator like =, <, >, <=, >=, or <>.
• array is the array you want to compare against.

This expression returns true only if the comparison is true for every element in the array.

sql

value operator ALL (array)

Example

This example shows how to check if a number is less than all elements in an array:

sql

SELECT 3 < ALL (ARRAY[5, 6, 7]) AS result;

SELECT 8 > ALL (ARRAY[5, 6, 7]) AS result;

SELECT 6 = ALL (ARRAY[6, 6, 6]) AS result;

SELECT 6 = ALL (ARRAY[6, 7, 6]) AS result;

Output

 result 
--------
 t

 result 
--------
 t

 result 
--------
 t

 result 
--------
 f

Common Pitfalls

One common mistake is confusing ALL with ANY. ALL requires the condition to be true for every element, while ANY requires it to be true for at least one element.

Another pitfall is using ALL with an empty array, which always returns true because there are no elements to contradict the condition.

sql

/* Wrong: Using ANY instead of ALL */
SELECT 3 < ANY (ARRAY[5, 6, 7]) AS result_wrong;

/* Correct: Using ALL */
SELECT 3 < ALL (ARRAY[5, 6, 7]) AS result_correct;

Output

 result_wrong 
--------------
 t

 result_correct 
---------------
 t

Quick Reference

Usage	Description
value = ALL (array)	True if value equals every element in the array
value < ALL (array)	True if value is less than every element in the array
value > ALL (array)	True if value is greater than every element in the array
value <> ALL (array)	True if value is different from every element in the array
Empty array with ALL	Always returns true because no elements contradict the condition

Key Takeaways

Use value operator ALL (array) to compare a value against every element in a PostgreSQL array.

ALL returns true only if the condition holds for all array elements.

Do not confuse ALL with ANY; they have opposite logic.

Using ALL with an empty array always returns true.

Common operators with ALL include =, <, >, <=, >=, and <>.