Re: field alias in where condition

=?ISO-8859-1?Q?Havasv=F6lgyi_Ott=F3?= <h(dot)otto(at)freemail(dot)hu> writes:
> select substring(proname from 1 to 1) as nevresz, count(*)
> from pg_proc
> where nevresz = 'a'
> order by nevresz
> group by nevresz;

> What is the problem? I cannot use column alias in where condition?

Exactly. Per the SQL spec, the WHERE condition is evaluated before the
SELECT list, so it makes no logical sense to do that. Consider for
example trying to avoid division-by-zero failure like this:

SELECT 1/x AS y WHERE x <> 0;

You'd be really unhappy if 1/x were computed so that it could be made
available in the WHERE condition.

The fact that you're allowed to refer to those aliases in ORDER BY/GROUP
BY is a historical accident stemming from the limited ORDER BY
facilities in ancient versions of the spec.

regards, tom lane