Finding the position of a value in PostgreSQL arrays

Question

How can I get the position of a value in PostgreSQL arrays? There's .index() method for Python and array_search() function for PHP, but I cannot find any such function for PostgreSQL. Should I write a stored function to do that? I prefer to solve by using a built-in function.

Answer 1

The documentation recommends using the generate_subscripts function. The function below emulate's PHP's array_search:

CREATE FUNCTION array_search(needle ANYELEMENT, haystack ANYARRAY)
RETURNS INT AS $$
    SELECT i
      FROM generate_subscripts($2, 1) AS i
     WHERE $2[i] = $1
  ORDER BY i
$$ LANGUAGE sql STABLE;

This returns the index of the first match, if present. If you want all matches, simply change RETURNS INT to RETURNS SETOF INT. This function, as is, returns NULL if no match is found.

This function only works with one-dimensional arrays.

Also, bear in mind that array_search(NULL, a) always returns NULL, even if the array contains null elements:

> SELECT array_search(null, array[1, 2, null, 4]);
 array_search 
--------------

(1 row)

This is because SQL considers NULL = NULL to be unknown (i.e. NULL). See functions-comparison. If you want array_search to be able to find NULL elements, change

     WHERE $2[i] = $1

to

     WHERE $2[i] IS NOT DISTINCT FROM $1