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