14

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?

18

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:

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
| improve this answer | |
  • 4
    Another solution would be to do SELECT id, electronic_mail FROM ( SELECT id, email AS electronic_mail FROM users ) AS t;. Fiddle available here. – Vérace Sep 8 '19 at 11:03
-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.

| improve this answer | |
  • 1
    What is that trim(a.b) thing? Why not simply a column rename (like SELECT id, email AS email_internal) in the internal query? – peterh - Reinstate Monica Dec 11 '19 at 17:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.