Postgresql Iterate over an array field and use the records for another query

Question

I'm trying to iterate over an array field in order to use every record as a parameter to query and finally join all results, but I need help to get it.

I have a table with an array field called fleets and it can have one or more values ie. {1,2,3} I need iterate over every value to get all vehicles belonging to these fleets.

With a subquery I'm getting 3 rows with these values 1,2,3

SELECT * FROM vehicles WHERE fleet_fk=(
   SELECT unnest(fleets) FROM auth_user WHERE id=4)

I'm using PostgreSQL 9.4.

Answer 1

If your query raises the ERROR: more than one row returned by a subquery used as an expression that means that you should use ANY:

SELECT * FROM vehicles 
WHERE fleet_fk = ANY(
   SELECT unnest(fleets) FROM auth_user WHERE id=4)

Answer 2

Since fleets is an array column you have a couple of options.

Either use the ANY construct directly (no need to unnest()):

SELECT * FROM vehicles 
WHERE fleet_fk = ANY(SELECT fleets FROM auth_user WHERE id = 4);

Or rewrite as join:

SELECT v.*
FROM   auth_user a
JOIN   vehicles  v ON v.fleet_fk = ANY(a.fleets)
WHERE  a.id = 4;

Or you can unnest(), then you don't need ANY any more:

SELECT v.*
FROM   auth_user a
     , unnest(a.fleets) fleet_fk   -- implicit LATERAL join
JOIN   vehicles  v USING (fleet_fk)
WHERE  a.id = 4;

This is assuming you don't have another column named fleet_fk in auth_user. Use the more explicit ON clause for the join in this case to avoid the ambiguity.

Be aware that there are two implementation for ANY.

• ANY for sets
• ANY for arrays

Behavior of the beasts is basically the same, you just feed them differently.

DB design

Consider normalizing the hidden many-to-many (or one-to-many?) relationship in your DB schema:

• How to implement a many-to-many relationship in PostgreSQL?