Using column alias in a WHERE clause doesn't work

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?

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
where  email = '';               -- works!

StackExchange Database Administrators Q#225874, answer score: 55