Re: How to use result column names in having cause

"chris smith" <dmagick(at)gmail(dot)com> writes:
> I assume it's this way because the standard says so..

Right. From a logical point of view, the HAVING clause has to be
evaluated before the output expressions are computed, so it doesn't
make any sense to expect the output expressions to be available in
HAVING. An example of why this must be so is
SELECT x, 1/avg(y) FROM TAB GROUP BY x HAVING avg(y) > 0
If the HAVING clause isn't executed first this may fail with zero-divide
errors.

The real bug here IMHO is that we don't enforce the same rule for
GROUP BY. Allowing "GROUP BY 1" to reference an output column is
a violation of the spec, which I think we adopted basically because
some other DBMSes do it too, but it's just as semantically nonsensical
as doing it in HAVING would be. It's a wart on the language that we
can't really get rid of because of backwards-compatibility
considerations, but we're highly unlikely to add more such warts.

BTW, if you're really intent on not writing your big expression twice,
use a sub-select:
SELECT x
FROM (SELECT big_expr AS x FROM ...) AS ss
GROUP BY ...
HAVING x > ...

regards, tom lane