Re: WHERE on an alias

Joseph Shraibman <jks@selectacast.net> writes:
> playpen=# select a, sum(b) as dsum from taba where dsum > 5 group by(a);
> ERROR:  Attribute 'dsum' not found

> Why can we GROUP BY on an alias but not do a WHERE on an alias?

Because WHERE is computed before the select's output list is.

Strictly speaking you shouldn't be able to GROUP on an alias either (the
SQL spec doesn't allow it).  We accept that for historical reasons only,
ie, our interpretation of GROUP used to be wrong and we didn't want to
break applications that relied on the wrong interpretation.

Note that writing a GROUP on an alias does *not* mean the alias is only
computed once.  It saves no computation, only writing out the expression
twice.

> I have a subselect that 
> explain shows is being run twice if I have to put it in the WHERE clause.

Possibly you could restructure your query into something with a
subselect in the FROM clause?
        regards, tom lane