Postgresql – How to do an =ANY(SELECT…query in postgresql

arraypostgresql

I'm trying to select all the latitudes and longitudes for a group of users based on their id being in an array stored in another table. Here's my attempt:

SELECT latitude, longitude 
FROM userloc WHERE id = ANY( SELECT interested FROM donedeals WHERE deals_id=67);

But it gives me the following error:

ERROR:  operator does not exist: integer = integer[]
LINE 1: SELECT latitude, longitude FROM userloc WHERE id = ANY( SELE...
                                                         ^
HINT:  No operator matches the given name and argument type(s).
       You might need to add explicit type casts.

donedeals has an int column for deals_id and an int array column for interested, which contains id's corresponding to the id column of userloc, which stores latitude and longitude:

 deals_id |  interested   
----------+---------------
       67 | {377,387,376}
       64 | {381,384}
       66 | {377,387}

  latitude  |  longitude  | id  
------------+-------------+-----
 40.6439417 |  -73.964927 | 384
 40.7554919 |  -73.925891 | 380
 40.6434067 | -73.9657654 | 385
  40.746452 |   -73.90732 | 378
  40.643459 |  -73.964586 | 381
 40.6430341 | -73.9656954 | 382

This is all in Postgres 9.3.5.

I'd like to select all latitudes and longitudes for id's corresponding to the interested array for a given deals_id. This seems like it should be doable in a single call, but I can't seem to figure out the syntax. Any recommendations would be greatly appreciated.

Best Answer

Unfortunately = ANY (array) only works with an array literal on the right hand side, not a sub-select.

You need to "normalize" your de-normalized model, using unnest():

SELECT latitude, longitude 
FROM userloc 
WHERE id IN (SELECT unnest(interested) 
             FROM donedeals 
             WHERE deals_id = 64);

If deals_id is unique in the donedeals table, another option is to "convert" the id on the left side to an array and then use the "is contained by" operator: <@: 

SELECT latitude, longitude 
FROM userloc 
WHERE array[id] <@ (SELECT interested 
                    FROM donedeals 
                    WHERE deals_id=64 );

Not sure which one would be faster. You will need to check the execution plan.

Related Question