PostgreSQL 18 reference · Array Functions

PostgreSQL ANY and ALL Array Operators

Compare a value with every element of an array: x = ANY(array) is true if any element matches. ALL needs every element to.

PostgreSQL 18

Create a free account to save this page, take notes and track your progress. Sign in

Syntax

value operator ANY (array)  |  value operator ALL (array)

Parameter	Description
`value`	The value to compare.
`array`	An array value.

boolean.

ANY and ALL

SELECT 'tue' = ANY(ARRAY['mon', 'tue']) AS is_member, 5 > ALL(ARRAY[1, 2, 3]) AS bigger_than_all;

Output

 is_member | bigger_than_all
-----------+-----------------
 t         | t
(1 row)

Run this on your computer — see “Get Started” for setup.

An array parameter instead of IN (…)

SELECT name FROM customers WHERE customer_id = ANY('{1,3,5}'::int[]);

Output

       name
-------------------
 Ada Lovelace
 Grace Hopper
 Margaret Hamilton
(3 rows)

Run this on your computer — see “Get Started” for setup.

@> tests containment for whole arrays: ARRAY[1,2,3] @> ARRAY[2,3] is true.

ARRAY_POSITION() · Return the 1-based position of the first occurrence of a value in an array, or NULL if it isn't there.
UNNEST() · Expand an array into a set of rows, one per element. With several arrays it zips them into columns.
SQL String Functions · tutorial

Was this page helpful?