Why do I get an error when filtering subSelect? select "masters".*, (select avg (rating) 
from "reviews" where "master_id" = "masters"."id") as rating 
from "masters" where rating &gt; 3



  [42703] ERROR: column "rating" does not exist
You can't use a column alias on the same level where you defined it. You need to wrap the query: 

select *
from (
  select masters.*, (select avg(rating) 
                     from reviews 
                     where master_id = masters.id) as rating 
  from masters 
) t
where rating &gt; 3;


A more efficient solution is however, to do the aggregation once for all rows and then join to the result:

select m.*, r.rating
from masters m
  join (
     select master_id, avg(rating)  as rating
     from reviews 
     group by master_id
     having avg(rating) &gt; 3
  ) r on r.master_id = m.id;