PostgreSQL 18 reference · Array Functions

PostgreSQL ARRAY_POSITION() Function

Return the 1-based position of the first occurrence of a value in an array, or NULL if it isn't there.

PostgreSQL 18 Since 9.5

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

Syntax

array_position(array, element, start)  |  array_positions(array, element)

integer, or NULL.

Find a value

SELECT array_position(ARRAY['mon', 'tue', 'wed'], 'tue') AS pos,
       array_position(ARRAY['mon'], 'sun') AS missing;

Output

 pos | missing
-----+---------
   2 |    NULL
(1 row)

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

Every position

SELECT array_positions(ARRAY[1, 2, 1, 3, 1], 1) AS all_positions;

Output

 all_positions
---------------
 {1,3,5}
(1 row)

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

Version	Change
PostgreSQL 9.5	Added

ANY and ALL · Compare a value with every element of an array: x = ANY(array) is true if any element matches. ALL needs every element to.
ARRAY_REMOVE() · Remove every element equal to a given value from an array. array_replace() swaps them for another value.
SQL String Functions · tutorial