CODEWITHSUNDEEP
postgresql
Eden
Eden

Reputation: 4196

Array and IN operator in PostgreSQL

I would like to know if there is a way to check that every item in an array can be found in a sub-select currently I've the following query:

SELECT * 
FROM   similarity_values 
WHERE  assemblies[1] IN 
       ( 
              SELECT 200) 
OR     assemblies[2] IN 
       ( 
              SELECT 200)

And I'm looking for a away to remove the OR and us just one sub select. Something along the lines of:

SELECT * FROM similarity_values WHERE assemblies[*] IN (SELECT 200)

2 Notes:

  1. The sub select (SELECT 200) is way more complicated
  2. the assemblies array contains up to 15 entries

Thanks, Eden

Upvotes: 0

Views: 613

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Try unnest:

SELECT * 
FROM   similarity_values 
WHERE  ( SELECT 200 ) IN 
          ( SELECT id 
            FROM unnest( assemblies ) AS id )

Upvotes: 0

user330315
user330315

Reputation:

You can convert the result of the subselect to an array and then use the "overlaps" or "contains" operator 

where assemblies @> array(select ...)

or

where assemblies && array(select ...)

The difference between those is the handling if the array on the right hand side (the result of your sub-select) returns more than one value. @> will only be true if all values from the sub-select are contained in assemblies. The && will be true if at least one value is found.

If the sub-select always returns exactly one row and one column, you could do the following: 

where (select ...) = ANY(assemblies)

Upvotes: 4

Related Questions