PostgreSQL array_position() Function

The PostgreSQL array_position() function finds the specified element in a specified array and returns subscript of the the first occurrence.

array_position() Syntax

Here is the syntax of the PostgreSQL array_position() function:

array_position(array, element[, start]) -> integer

Parameters

array

Required. The array to search. It must be a one-dimensional array.

element

Required. The element to search for. The type of the elements must match the data type of the array.

start

Required. The subscript where the search starts.

Return value

The PostgreSQL array_position() function finds the specified element in a specified array and returns subscript of the the first occurrence. Returns NULL if the specified element is not found.

If the data type of the searched element is different from the array, the array_position() function will return an error.

array_position() Examples

This example shows how to use the PostgreSQL array_position() function to find elements from an array.

SELECT array_position(ARRAY[0, 1, 2], 1);

 array_position
----------------
              2

You can specify the index where the search starts, for example:

SELECT array_position(ARRAY[0, 1, 2, 1, 2], 1, 3);

 array_position
----------------
              4

Here, although the first 1 is at index 2, but it returns 4 because the search starts the index 3.