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

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.