array_position()

A function returning the first occurrence of a value in an array

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

array_position() was added in PostgreSQL 9.5.

Usage

array_position ( anyarray, anyelement [, integer ] ) → integer

The provided array can only have one dimension.

To search for all occurrences of a value, use array_positions().

Change history

Examples

Basic usage example for array_position():

postgres=# SELECT array_position(ARRAY[1,2,3], 2);
 array_position 
----------------
              2
(1 row)

Finding the first occurrent at or after the provided subscript:

postgres=# SELECT array_position(ARRAY['foo','bar','baz','boo','foo'], 'foo', 3);
 array_position 
----------------
              5
(1 row)

It is possible to search for a NULL value:

postgres=# SELECT array_position(ARRAY[1,2,3,NULL,5], NULL);
 array_position 
----------------
              4
(1 row)

Attempting to use a multi-dimensional array:

postgres=# SELECT array_position(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_positions()