Contents
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
- PostgreSQL 9.5
- added (commit 13dbc7a8)
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
References
- PostgreSQL documentation: Array Functions