Re: aliases, &c in HAVING clause?

David Fetter <david(at)fetter(dot)org> writes:
> Just in general, isn't it better to write a piece of code (here, a
> possibly-complicated aggregate) just once and refer to it elsewhere
> rather than have to write a separate copy of it everywhere it's used?

In general, you do that with subselects. Having inconsistent scoping
rules for ORDER BY is a much less desirable way to attack it.

SELECT * FROM
(SELECT foobar(baz) AS x FROM ...) ss
GROUP BY x HAVING x > 44;

>> But the real reason why this is bogus is that it violates the
>> fundamental conceptual model of how SELECT works. The SELECT output
>> list is not supposed to be computed until after all the other steps
>> are complete, and therefore it's improper to assume its results are
>> available in GROUP BY or HAVING.

> but I'm pretty certain that PostgreSQL doesn't do things that way at
> the implementation level.

It does anywhere that you can tell the difference. Try a SELECT with
side-effect-producing output expressions. As an example, would you be
happy if the following were prone to getting divide-by-zero errors?

SELECT x, 1.0 / sum(x) FROM t GROUP BY x HAVING sum(x) != 0;

regards, tom lane