Using column alias in a WHERE clause doesn't work

Question

Given a table users with two fields: id and email.

select id, email as electronic_mail 
from (  
        select id, email 
        from users
) t 
where electronic_mail = ''

Postgres complains that:

ERROR:  column "electronic_mail" does not exist

The example is just to demonstrate the arising problem. My actual case is more complex, I iterate though an array of elements in a json column, taking a single scalar value from each. (I can share some code if that helps.)

I really don't get what would be the complication, probably I'm unaware of something. I was under the impression that aliased columns can be employed in a WHERE clause without problem?

Answer 1

The manual clarifies here:

An output column's name can be used to refer to the column's value in ORDER BY and GROUP BY clauses, but not in the WHERE or HAVING clauses; there you must write out the expression instead.

That's according to the SQL standard and may not be very intuitive. The (historic) reason behind this is the sequence of events in a SELECT query. WHERE and HAVING are resolved before column aliases are considered, while GROUP BY and ORDER BY happen later, after column aliases have been applied.
Also note that conflicts between input and output names are resolved differently in ORDER BY and GROUP BY - another historic oddity (with a reason behind it, but potentially confusing nonetheless). See:

• PostgreSQL: How to return rows with respect to a found row (relative results)?

Best to avoid column aliases that conflict with input column names a priori.

Aside: the subquery in your example is just noise since the WHERE clause is part of the outer query, so the example can be simplified to:

select id, email as electronic_mail 
from users t 
where electronic_mail = '';  -- doesn't work

Answer 2

The answer was sort of posted in a comment but I had a similar problem and was directed here, figured I would share as an answer. Your alias should appear in the inner select, not the outer.

select t.id, t.electronic_mail 
from (  
        select id, email as electronic_mail
        from users
) t 
where t.electronic_mail = '';

For a real-world example (i.e. this example can be simplified by just not including an alias at all):

select tb.lei, tb.lar_count 
from (
       select distinct(lei), count(*) as lar_count 
       from hmda_tb 
       where tract_id = 8029964900 
       group by lei
     ) as tb 
where tb.lar_count >= 10;

Answer 3

I know this is a very old post - but just came across it - what I have done in the past is to "transform" the field - say using trim(a.b) as C,

this seems to force the alias to take.