array_positions()

A function returning each occurrence of a value in an array

array_positions() is a system function returning the subscript (position) of each occurrence of the provided value in an array. A starting subscript can be optionally provided.

array_positions() was added in PostgreSQL 9.5.

Usage

array_positions ( anyarray, anyelement ) → integer[]

The provided array can only have one dimension.

To search for the first occurrence of a value only, use array_position().

Change history

Examples

Basic usage example for array_positions():

postgres=# SELECT array_positions(ARRAY[1,2,3,2,1], 1);
 array_positions 
-----------------
 {1,5}
(1 row)

It is possible to search for NULL values:

postgres=# SELECT array_positions(ARRAY[1,2,NULL,2,NULL], NULL);
 array_positions 
-----------------
 {3,5}
(1 row)

Attempting to use a multi-dimensional array:

postgres=# SELECT array_positions(ARRAY[⁣[1,2,3],[1,2,3]⁣], 2);
ERROR:  searching for elements in multidimensional arrays is not supported

Categories

Arrays, System function

See also

array_position()