mercredi 28 janvier 2015

How to do an =ANY(SELECT...query in postgresql?


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 postgresql 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. Thank you!





Aucun commentaire:

Enregistrer un commentaire