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

In PostgreSQL, you can use ANY with an array to check if a value matches any element in that array using the syntax value = ANY(array). This returns true if the value is equal to at least one element in the array. It is useful for filtering rows where a column matches any item in a list stored as an array.

Syntax

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

• value = ANY(array): Checks if value equals any element in array.
• value <> ALL(array): Checks if value is not equal to any element in array.
• value > ANY(array), value < ANY(array), etc.: You can use other comparison operators similarly.

The array must be a PostgreSQL array type, and value is compared against each element.

sql

SELECT 5 = ANY(ARRAY[1, 3, 5, 7]);
-- Returns true because 5 is in the array

SELECT 'apple' = ANY(ARRAY['banana', 'apple', 'cherry']);
-- Returns true because 'apple' is in the array

Output

 ?column? 
----------
 t
(1 row)

 ?column? 
----------
 t
(1 row)

Example

This example shows how to filter rows from a table where a column value matches any element in an array using ANY.

sql

CREATE TEMP TABLE fruits (name TEXT);
INSERT INTO fruits VALUES ('apple'), ('banana'), ('cherry'), ('date');

SELECT name
FROM fruits
WHERE name = ANY(ARRAY['banana', 'date', 'fig']);

Output

 name  
--------
 banana
 date
(2 rows)

Common Pitfalls

Common mistakes when using ANY with arrays include:

• Using IN instead of = ANY when the right side is an array (they are different; IN expects a list of values, not an array).
• Forgetting that ANY compares the left value to each element individually, so the array must be properly typed.
• Using ANY with NULL arrays or NULL elements can lead to unexpected results because comparisons with NULL return NULL (unknown).

Example of wrong and right usage:

sql

SELECT 5 IN (ARRAY[1,3,5]); -- Wrong: returns false because IN expects a list, not an array

SELECT 5 = ANY(ARRAY[1,3,5]); -- Correct: returns true

Output

 ?column? 
----------
 f
(1 row)

 ?column? 
----------
 t
(1 row)

Quick Reference

Usage	Description	Example
value = ANY(array)	True if value equals any element in array	5 = ANY(ARRAY[1,5,9]) returns true
value <> ALL(array)	True if value is not equal to any element	5 <> ALL(ARRAY[1,3,5]) returns false
value > ANY(array)	True if value is greater than any element	7 > ANY(ARRAY[3,5,9]) returns true
value < ANY(array)	True if value is less than any element	2 < ANY(ARRAY[3,5,9]) returns true

Key Takeaways

Use = ANY(array) to check if a value matches any element in a PostgreSQL array.

ANY works with all comparison operators like =, <>, >, <, etc.

Do not confuse IN with = ANY; IN expects a list, not an array.

Be careful with NULL values in arrays as they can affect comparison results.

Using ANY with arrays is efficient for filtering rows against multiple values stored as arrays.