"chris smith" <dmagick@xxxxxxxxx> 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
