Check if value exists in Postgres array

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!

Solution 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

Solution 2:

Watch out for the trap I got into: When checking if certain value is not present in an array, you shouldn't do:

SELECT value_variable != ANY('{1,2,3}'::int[])

but use

SELECT value_variable != ALL('{1,2,3}'::int[])

instead.

Solution 3:

but if you have other ways to do it please share.

You can compare two arrays. If any of the values in the left array overlap the values in the right array, then it returns true. It's kind of hackish, but it works.

SELECT '{1}'   && '{1,2,3}'::int[];  -- true
SELECT '{1,4}' && '{1,2,3}'::int[];  -- true
SELECT '{4}'   && '{1,2,3}'::int[];  -- false

• In the first and second query, value 1 is in the right array
• Notice that the second query is true, even though the value 4 is not contained in the right array
• For the third query, no values in the left array (i.e., 4) are in the right array, so it returns false

Solution 4:

unnest can be used as well. It expands array to a set of rows and then simply checking a value exists or not is as simple as using IN or NOT IN.

e.g.

1. id => uuid

2. exception_list_ids => uuid[]

select * from table where id NOT IN (select unnest(exception_list_ids) from table2)

Solution 5:

Hi that one works fine for me, maybe useful for someone

select * from your_table where array_column ::text ilike ANY (ARRAY['%text_to_search%'::text]);