Array and IN operator in PostgreSQL

Question

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:

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

Thanks, Eden

user330315 · Accepted Answer · 2019-03-06 14:25:02Z

4

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)

answered Mar 6, 2019 at 14:25

user330315

Sign up to request clarification or add additional context in comments.

Comments

Juan Carlos Oropeza · Accepted Answer · 2019-03-06 14:25:12Z

0

Try unnest:

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

answered Mar 6, 2019 at 14:25

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Collectives™ on Stack Overflow

Array and IN operator in PostgreSQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related