在PostgreSQL数组中查找值的位置

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.

如何在PostgreSQL数组中获取值的位置？ Python的.index（）方法和PHP的array_search（）函数，但我找不到PostgreSQL的任何这样的函数。我应该写一个存储函数来做到这一点吗？我更喜欢使用内置函数来解决。

3 个解决方案

#1

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.

从版本9.5开始，内置函数：array_position（）和array_positions（），用于按值搜索数组键（仅第一次出现）或键（所有出现次数）。

These functions supports anyarray type.

这些函数支持anyarray类型。

#2

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

文档建议使用generate_subscripts函数。下面的函数模拟PHP的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.

这将返回第一个匹配的索引（如果存在）。如果您想要所有匹配，只需将RETURNS INT更改为RETURNS SETOF INT。如果没有找到匹配项，则此函数返回NULL。

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:

另外，请记住，即使数组包含null元素，array_search（NULL，a）也总是返回NULL：

> 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

这是因为SQL认为NULL = NULL是未知的（即NULL）。参见函数比较。如果您希望array_search能够找到NULL元素，请更改

     WHERE $2[i] = $1

至

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

#3

For integer arrays only you can use the greatly faster idx function from the intarray bundled extension.

对于整数数组，只能使用intarray捆绑扩展中更快的idx函数。

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.

遗憾的是，这个函数尚未推广到支持所有数组类型，因此您对其他数组采用非常慢的SQL方法。

#1