Question
Using Postgres 9.0, 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 can't see it. This seems better:
select '{1,2,3}'::int[] @> ARRAY[value_variable::int]
I believe it will suffice. But if you have other ways to do it, please share!
Answer
Simpler with the ANY construct:
SELECT value_variable = ANY ('{1,2,3}'::int[])
The right operand of ANY (between parentheses) can either be a [set
(result of a subquery, for
instance)](https://www.postgresql.org/docs/current/functions-
subquery.html#FUNCTIONS-SUBQUERY-ANY-SOME) or an
[array](https://www.postgresql.org/docs/current/functions-
comparisons.html#id-1.5.8.28.16). There are several ways to use it:
Important difference: [Array operators (<@, @>, && et
al.)](https://www.postgresql.org/docs/current/functions-array.html#ARRAY-
OPERATORS-TABLE) 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 can be supported with a plain B-tree index
(with the indexed expression to the left of the operator, not the other way
round like it seems to be in your example). Example:
None of this works for NULL elements. To test for NULL:
