检查Postgres里的数组存在价值 [英] Check if value exists in Postgres array
问题描述
我需要一种方法来测试,如果给定的数组中存在的值。到目前为止,我想出了这样的事情。
I need a way to test if a value exists in a given array. So far I came up with something like this
select '{1,2,3}'::int[] @> (ARRAY[]::int[] || value_variable::int)
但我一直在想应该有这样一个简单的方法,我只是不能看到它。
but I keep thinking there should be a simpler way to this, I just cant see it.
编辑:刚刚意识到,我可以做到这一点。
Just realized I could do this
select '{1,2,3}'::int[] @> ARRAY[value_variable::int]
这是好多了,我相信就足够了,但如果你有其他的方式来做到这一点,请大家分享。
This is much better and I believe will suffice, but if you have other ways to do it please share.
推荐答案
与<一个简单href=\"http://www.postgresql.org/docs/current/interactive/functions-comparisons.html#AEN19712\"><$c$c>ANY$c$c>构建:
SELECT value_variable = ANY ('{1,2,3}'::int[])
的右操作数任何
(括号之间)可以是一个<一个href=\"http://www.postgresql.org/docs/current/interactive/functions-subquery.html#FUNCTIONS-SUBQUERY-ANY-SOME\">set (子查询结果,例如)的或的一个<一href=\"http://www.postgresql.org/docs/current/interactive/functions-comparisons.html#AEN19712\">array.有几种方法来使用它:
The right operand of ANY
(between parentheses) can either be a set (result of a subquery, for instance) or an array. There are several ways to use it:
- SQLAlchemy: how to filter on PgArray column types?
重要提示的区别:<一href=\"http://www.postgresql.org/docs/current/interactive/functions-array.html#ARRAY-OPERATORS-TABLE\">Array运算符(&LT; @
, @方式&gt;
等)期望的阵列类型,操作数和支持GIN和GiST索引PostgreSQL中的标准分发的,而任何
构造期望一个的元素的类型为左操作数,不支持这些指标。例如:
Important difference: Array operators (<@
, @>
et al.) expect array types as operands and support GIN or GiST indices in the standard distribution of PostgreSQL, while the ANY
construct expects an element type as left operand and does not support these indices. Example:
- Index在一个JSON数组 找到一个元素
- Index for finding an element in a JSON array
这一切都不适用于 NULL
元素。为了测试 NULL
:
None of this works for NULL
elements. To test for NULL
:
- Check if NULL exists in Postgres array
这篇关于检查Postgres里的数组存在价值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!