Select polygons that are within other polygons and have a certain area in PostGIS

Question

I'm using PostgreSQL 14.1 with PostGIS 3.1 and I've got two tables malla_p and pen_bal. I want to select those polygons of table malla_p smaller than 10,000 ha that are within the polygons of table pen_bal. I've built the following query:

select 
    m.utmcode , cast(st_area(m.geom)/10000 as integer) ha
    from malla_p m join pen_bal c
    on st_within(m.geom, c.geom) 
    group by m.utmcode
    having ha < 10000
    order by ha desc
;

but it returns an error saying that column "ha" doesn´t exist. What's wrong?

Answer 1

The issue is that you're referencing a field that you just created and named. You'll need to use a CTE or subquery to use the ha field in your query:

with cte as (select 
    m.utmcode , cast(st_area(m.geom)/10000 as integer) ha
    from malla_p m join pen_bal c
    on st_within(m.geom, c.geom) 
    group by m.utmcode, m.geom)

select * from cte where ha < 10000
order by ha desc