30

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?

0

3 Answers 3

39

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
1
  • 8
    Another solution would be to do SELECT id, electronic_mail FROM ( SELECT id, email AS electronic_mail FROM users ) AS t;. Fiddle available here. Sep 8, 2019 at 11:03
1

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;
1
  • indeed darraths, thanks for sharing!
    – Victor
    Jun 16 at 17:41
-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.

1
  • 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
    Dec 11, 2019 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.