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

• Vérace: Another solution would be to do SELECT id, electronic_mail FROM ( SELECT id, email AS electronic_mail FROM users ) AS t;. Fiddle available here.

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 am planning to migrate a large application from SAP (formerly Sybase) SQL Anywhere to Postgres. SQL Anywhere does let you use an Alias in the WHERE clause. That has been supported for over 10 years, and we use it a lot to write nice, clear queries.

See: https://help.sap.com/doc/06af1adfbfbc491fabc14f005dfe15dc/17.0/en-US/SQL-Anywhere-Server-Users-Guide-en.pdf

Page 173 says: Name space occlusion Aliases can be used anywhere in the SELECT block in which they are defined, including other SELECT list expressions that in turn define additional aliases. Cyclic alias references are not permitted. If the alias specified for an expression is identical to the name of a column or variable in the name space of the SELECT block, the alias definition occludes the column or variable.

The manual shows this example, which is legal in SQL Anywhere: SELECT DepartmentID AS DepartmentName FROM Departments WHERE DepartmentName = 'Marketing'

I can provide additional proof that this works. Here is a bug report: https://sqlanywhere-forum.sap.com/questions/28899/select-aliases-not-working-properly-inside-other-statements-referenced-by-a-where-clause

My existing application uses this feature extensively.

I have seen a lot of other posts asking to use an Alias in the WHERE clause, not only in Postgres but for SQL Server, Oracle and mySQL.

In my many years using SQL Server, I have run into the need to write duplicate expressions many, many times, and it seems like something which the parser could be enhanced to handle.

Basically, the feature set which SQL Anywhere has is:

1. Allow a Column Alias in the WHERE clause
2. Allow a Column Alias in the HAVING clause
3. Allow a Column Alias in the GROUP BY clause (Postgres already allows this)
4. Allow a Column Alias in the ORDER BY clause (Postgres already allows this)

I know how to write CTEs, and I think they are very nice, so yes, there is a workaround. And yes, I see the syntax above, which is OK.

Sincerely, Victor Reinhart

• John K. N.: Welcome to the DBA.SE community. The question is about PostgreSQL and not about SQL Anywhere or SQL Server. Your contribution does supply an answer for issues with SQL Anywhere. but fails to answer the PostgreSQL issue. Could you be a bit more specific?

Answer 4

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.

• peterh: What is that trim(a.b) thing? Why not simply a column rename (like SELECT id, email AS email_internal) in the internal query?