在 PostgreSQL 数组中查找值的位置
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8798055/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Finding the position of a value in PostgreSQL arrays
提问by minhee
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 数组中值的位置？有.index()Python的方法和array_search()PHP 的函数，但我找不到 PostgreSQL 的任何此类函数。我应该编写一个存储函数来做到这一点吗？我更喜欢使用内置函数来解决。
采纳答案by Oto Shavadze
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()and array_positions()，用于按值搜索数组键（仅第一次出现）或键（所有出现）。
These functions supports anyarray type.
这些函数支持 anyarray 类型。
回答by Joey Adams
The documentation recommendsusing the generate_subscriptsfunction. 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(, 1) AS i
WHERE [i] =
ORDER BY i
$$ LANGUAGE sql STABLE;
This returns the index of the first match, if present. If you want all matches, simply change RETURNS INTto RETURNS SETOF INT. This function, as is, returns NULLif 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:
另外，请记住，即使数组包含空元素，也array_search(NULL, a)始终返回NULL：
> SELECT array_search(null, array[1, 2, null, 4]);
array_search
--------------
(1 row)
This is because SQL considers NULL = NULLto be unknown(i.e. NULL). See functions-comparison. If you want array_searchto be able to find NULLelements, change
这是因为 SQL 认为NULL = NULL是未知的（即NULL）。请参阅功能比较。如果您希望array_search能够找到NULL元素，请更改
WHERE [i] =
to
到
WHERE [i] IS NOT DISTINCT FROM
回答by Craig Ringer
For integer arrays onlyyou can use the greatly faster idxfunction from the intarraybundled extension.
仅对于整数数组，您可以使用捆绑扩展中速度更快的idx函数intarray。
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 方法中。
