current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 27 down vote favorite
8

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

3 Answers 3

up vote 47 down vote accepted

Simpler with the ANY construct:

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

There are several ways:

Important difference: Array operators (<@, @> et al.) support GIN or GiST indices in the standard distribution of PostgreSQL, while the ANY construct does not. Here is an example:

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
    
where S.employee_id <@ ANY ('"+employeeIDsArray+"'::int[]) This returns PSQLException: ERROR: missing dimension value –  Ramprasad Mar 29 '14 at 13:11
    
@Ramprasad: s.employee_id <@ '+list_of_employeeIDs+'::int[] - no ANY with the <@ operator. –  Erwin Brandstetter Mar 29 '14 at 13:18
    
@ErwinBrandstetter Brandstetter Again same error occurs.Here list_of_employeeIDs means list or array? I try to pass scala array variable –  Ramprasad Mar 31 '14 at 4:55
up vote 13 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
    
Kind of a double negative; notice his use of ALL vs ANY –  vol7ron Nov 13 '13 at 15:56
2  
SELECT NOT value_variable = ANY('{1,2,3}'::int[]) might be more readable –  Ondřej Bouda Jun 4 '14 at 9:34
up vote 0 down vote

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 vlaues 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, despite the value 4 not being 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.
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.