postgres select where id in array selected from a different table

Question

Question from a postgres noob. I have two tables in postgres. See below for schematic.

strains_snps::table
    name::str
    variants_id::int array

variants::table
    id::int
    ref_base::str

I want to

select variants_id 
from strains_snps 
where strains_snps.name = 'foo'

and then use that variants_id (which is an int array) in a follow up query.

select * 
from variants 
where id in the_output_from_previous_query

Coming from python, I would assign the output of the first query to a variable, and then check for membership of that variable in the second query. But that probably isn't the best way here, and I hope there is some way to get this working as a single query?

EDIT

@Sumit suggested using a sub-query. I tried this, but without success.

select * from variants 
where id in 
(select variants_id from strains_snps where name = 'BMD1964')

The error pgadmin returned was

ERROR:  operator does not exist: integer = integer[]
LINE 2: where id in 
             ^
HINT:  No operator matches the given name and argument type(s). You  
might need to add explicit type casts.

********** Error **********

ERROR: operator does not exist: integer = integer[]
SQL state: 42883
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Character: 34

Please read meta.stackoverflow.com/questions/285551/… and the accepted answer — user330315
– user330315, Commented Jun 16, 2017 at 5:38

Piotr Rogowski · Accepted Answer · 2017-06-16 06:02:48Z

14

try this:

select * 
from variants 
where id in (
    select unnest(variants_id) 
    from strains_snps 
    where strains_snps.name = 'foo' 
)

answered Jun 16, 2017 at 6:02

Piotr Rogowski

3,90021 silver badges24 bronze badges

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

3 Comments

flashton Over a year ago

Works a treat! Thanks very much!

Zon Over a year ago

And if I have subquery extracted into WITH area?

Surya Over a year ago

can we not use @> or <@

Zon · Accepted Answer · 2020-01-24 08:52:10Z

0

And if you want it separated using WITH statement:

WITH ids AS (
  SELECT UNNEST(variants_id) 
  FROM strains_snps
  WHERE strains_snps.name = 'foo' )
SELECT *
FROM variants
WHERE id IN (SELECT * FROM ids);

answered Jan 24, 2020 at 8:52

Zon

20k8 gold badges99 silver badges104 bronze badges

Collectives™ on Stack Overflow

postgres select where id in array selected from a different table

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related