Check if value exists in Postgres array

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 1

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) or an array. There are several ways to use it:

• SQLAlchemy: how to filter on PgArray column types?
• IN vs ANY operator in 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 for finding an element in a JSON array

None of this works for NULL elements. To test for NULL:

• Check if NULL exists in Postgres array