PostgreSQL has an array_positions() function that returns the position of the all occurrences of a given value in an array.

There’s also a array_position() function (singular) that returns the position of just the first occurrence. So to get just the first occurrence, use that function instead.

Example

Here’s an example to demonstrate how array_positions() works:

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

Result:

{2,4,6}

We can see that Dog is included three times in the array, and so the subscript of all three occurrences were returned in an array.

No Match

If the specified value doesn’t exist in the array, an empty array is returned:

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

Result:

{}

Multi Dimensional Arrays

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

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

Result:

ERROR:  searching for elements in multidimensional arrays is not supported

Searching for NULL

It’s possible to search for NULL:

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

Result:

{2,5}