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.
Add a comment
|
3 Answers
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
1 Comment
Craig Ringer
Nice answer. This will get simpler and more efficient when PostgreSQL 9.4 adds
WITH ORDINAL functions, allowing you to just write unnest(the_array) WITH ORDINAL. Maybe I should write and submit a proper idx function for 9.4, though...Since version 9.5, there is built in functions: array_position() and array_positions(), for searching array key(only first occurrence) or keys(all occurrences), by value.
These functions supports anyarray type.
Comments
For integer arrays only you can use the greatly faster idx function from the intarray bundled extension.
This function hasn't been generalized to support all array types yet, unfortunately, so you're stuck with a very slow SQL approach for other arrays.
