Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 4 down vote favorite
1

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 cant see it.

Edit: Just realized I could do this

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

This is much better and I believe will suffice, but if you have other ways to do it please share.

share|improve this question

2 Answers

up vote 12 down vote accepted

Simpler:

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

Details in the manual here.

share|improve this answer
 
Thanks. Must have skipped that part of manual. This works great. It has a side effect of automatic casting. Ex: SELECT 1::smallint = ANY ('{1,2,3}'::int[]) works. Just make sure to put ANY() on the right side of expression. –  Mike Starov Jun 27 '12 at 23:52
 
Thanks for the answer. Got a problem where my query worked on local, but in heroku was throwing this message ANY/ALL (array) requires array on right side, the add of ::int[] did the charm. –  kinduff Dec 13 '12 at 19:14
up vote 2 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.