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