PostgreSQL has a couple of functions that enable us to get the position or positions of a given value in an array. One of them – array_position() – returns the position of just the first occurrence (as an integer), while the other function – array_positions() – returns the position of all occurrences (as an array).

So these functions are aptly named – one is singular and the other is plural.

But this isn’t the only difference. The array_position() function allows us to specify a start position, while array_positions() doesn’t.

Example

Here’s an example of both functions side by side:

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

Result:

 array_position | array_positions 
----------------+-----------------
              2 | {2,4,6}

As expected, array_position() returned the subscript of the first occurrence as an integer. The array_positions() function, on the other hand, returned an array of the subscripts of all occurrences.

Specifying a Start Position

Another difference between these two functions is that the array_position() function allows us to specify a start position. But that I mean it allows us to specify a subscript for which to start searching all subsequent values.

Here’s an example of what I mean:

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

Result:

4

In this case I specified 3, meaning that it would skip forward to the third element and then start searching from there. This meant that it skipped the first occurrence and picked the second occurrence instead (but only because that second occurrence is after our start point).

The array_positions() function, on the other hand, doesn’t allow us to do such things. Here’s what happens if I try to do the same thing with that function:

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

Result:

ERROR:  function array_positions(text[], unknown, integer) does not exist
LINE 2:     array_positions(
            ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.