Postgresql: How query using array values from another table?

Question

I'm working with an existent database that have an array field (Phones) with the IDs from another table, I'm new to this kind of feature, how can I get all records of Public_Phone that are recorded into User->Phones Array?

User Table

+---------------------------------------+
|ID | User  | Phones(int array)  | Email| 
+---------------------------------------+
| 1 | 11922 | {12,23,56}         | none |

Public_Phone Table

+-------------------------------------+
|ID | Location | Color | AR | Line_FK |
+-------------------------------------+

Note: The database was upgraded recently to 9.4
Thanks in advance.

Taking in count the @nullReference comments I'm trying this

SELECT * 
FROM Public_Phone pp
WHERE pp.id IN (1,5)

That's work

But querying for user[3] using a subquery:

SELECT * 
FROM Public_Phone pp
WHERE pp.id IN( SELECT Phones FROM User u WHERE u.id=3)

I got:

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

Using

SELECT * 
FROM Public_Phone pp
WHERE pp.id = ANY( SELECT Phones FROM User u WHERE u.id=3)

Is the same

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[]

Seems that is needed cast the field in order to IN can get it :-( How can I do that? — Goku
– Goku, Commented Mar 11, 2015 at 18:19
You might try the ANY command vs IN as described in this post: dba.stackexchange.com/questions/61520/… — whitwhoa
– whitwhoa, Commented Mar 11, 2015 at 18:27
I just updated my question with my test, I can't put this to work yet :-( — Goku
– Goku, Commented Mar 11, 2015 at 18:47

whitwhoa · Accepted Answer · 2015-03-11 19:44:02Z

3

Try the following query:

SELECT *
FROM Public_Phone pp
JOIN User u ON pp.id = ANY(u.Phones)
WHERE u.id = 3

edited Mar 11, 2015 at 19:44

answered Mar 11, 2015 at 19:15

whitwhoa

2,4994 gold badges36 silver badges63 bronze badges

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

Comments

Rudra Roy · Accepted Answer · 2021-02-13 20:36:05Z

0

This can be done more efficiently without a costly join. All you needed to do is use UNNEST to convert the array into rows

SELECT * 
FROM Public_Phone
WHERE id IN( SELECT UNNEST("Phones") FROM User WHERE id = 3 )

Reference https://lerner.co.il/2014/05/24/turning-postgresql-arrays-rows-unnest/

answered Feb 13, 2021 at 20:36

Rudra Roy

711 silver badge10 bronze badges

Collectives™ on Stack Overflow

Postgresql: How query using array values from another table?

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related