How ARRAY_POSITION() Works in PostgreSQL

PostgreSQL has an array_position() function that returns the position of the first occurrence of a given value in an array.

We have the option to specify a starting position, so that the function returns the position of the first occurrence after that.

To get all occurrences, use array_positions() instead.

Example

Here’s an example to demonstrate:

SELECT array_position(
    ARRAY[ 'Cat', 'Dog', 'Horse', 'Dog', 'Cat', 'Dog', 'Horse' ], 
    'Horse'
    );

Result:

We can see that even though Horse is included twice in the array, only the subscript of the first occurrence was returned.

As mentioned, to get the positions of all occurrences we can use array_positions() (plural) instead.

If the specified element doesn’t exist, NULL is returned:

SELECT array_position(
    ARRAY[ 'Cat', 'Dog', 'Horse', 'Dog', 'Cat', 'Dog', 'Horse' ], 
    'Buffalo'
    );

Result:

NULL

We have the option of providing a third argument to specify the starting position:

SELECT array_position(
    ARRAY[ 'Cat', 'Dog', 'Horse', 'Dog', 'Cat', 'Dog', 'Horse' ], 
    'Horse', 
    4
    );

Result:

Here I specified 4 as the start position, and so the function returned the subscript of the next occurrence from that point.

This means that the function could return NULL, even though the value is in the array (but before the starting position):

SELECT array_position(
    ARRAY[ 'Cat', 'Dog', 'Horse', 'Dog', 'Cat', 'Dog', 'Horse' ], 
    'Cat', 
    6
    );

Result:

NULL

Multi dimensional arrays aren’t supported (at least, not at the time of this writing). Therefore, the following returns an error:

SELECT array_position(
    ARRAY[ARRAY[ 'Cat', 'Dog', 'Horse', 'Dog', 'Cat', 'Dog', 'Horse' ]], 
    'Cat'
    );

Result:

ERROR:  searching for elements in multidimensional arrays is not supported

It’s possible to search for NULL:

SELECT array_position(
    ARRAY[ 'Cat', NULL, 'Horse', 'Dog', 'Cat', 'Dog', 'Horse' ], 
    NULL
    );

Result: