Coder Perfect

Check if the value is present in the Postgres array.


I need a mechanism to check if a value exists in a given array in Postgres 9.0. So far, I’ve come up with anything along the lines of

select '{1,2,3}'::int[] @> (ARRAY[]::int[] || value_variable::int)

But I keep thinking there must be an easier solution, but I can’t think of one. This appears to be preferable:

select '{1,2,3}'::int[] @> ARRAY[value_variable::int]

It should suffice, in my opinion. However, if you know of any alternative methods, please share them!

Asked by Mike Starov

Solution #1

With the ANY construct, things are a lot easier:

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

The right operand of ANY (between parentheses) can be either a set (for example, the result of a subquery) or an array. It can be used in a variety of ways:

Important distinction: In the normal PostgreSQL distribution, array operators (@, @>, &&, etc.) assume array types as operands and support GIN or GiST indices, whereas the ANY construct expects an element type as left operand and does not support these indices. Example:

For NULL items, none of this works. To check for NULL, do the following:

Answered by Erwin Brandstetter

Solution #2

Keep an eye out for the trap I fell into: You shouldn’t do the following when verifying if a value isn’t present in an array:

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

but use

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


Answered by murison

Solution #3

Two arrays can be compared. It returns true if any of the values in the left array overlap the values in the right array. It’s a little clumsy, but it gets the job done.

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

Answered by vol7ron

Solution #4

It’s also possible to use unnest. It expands an array to a collection of rows, and then checks whether or not a value exists by using IN or NOT IN.


(select unnest(exception list ids) from table2) select * from table where id NOT IN

Answered by pg2286

Solution #5

Hello, that one works perfectly for me, and it might be good for someone else.

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

Answered by Dave Kraczo

Post is based on