PostgreSQL array_positions() Function

The PostgreSQL array_positions() function finds the specified element in a specified array and returns subscripts of all occurrences as an array.

array_positions() Syntax

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

array_positions(array, element) -> integer

Parameters

array

Required. The array to search from. 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.

Return value

The PostgreSQL array_positions() function finds the specified element in a specified array and returns subscripts of all occurrences as an array. Returns NULL if the specified element is not found.

If the data type of the searched element and the array do not match, the array_positions() function will return an error.

array_positions() Examples

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

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

 array_positions
-----------------
 {2,4}

Here, the result array is {2,4}, it explanded that 1 appears at subscripts 2 and 4 in the array [0, 1, 2, 1, 2].