Re: Alias in the HAVING clause

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Robert Treat <xzilla(at)users(dot)sourceforge(dot)net>
Cc: pgsql-general(at)postgresql(dot)org, "Scott Marlowe" <scott(dot)marlowe(at)gmail(dot)com>, "Nathan Thatcher" <n8thatcher(at)gmail(dot)com>
Subject: Re: Alias in the HAVING clause
Date: 2008-05-14 16:05:50
Message-ID: 20580.1210781150@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> writes:
> ... but I have to wonder, if we have established f1 by the time
> we evaluate the group by, shouldn't we also be able to determine f1 at having
> time, and therefore allow alias in having in this instance?

The key point is that we only allow output-list aliases (in either
GROUP BY or ORDER BY) as the *single* component of a grouping/ordering
item --- that is, these are equivalent to the "ORDER BY 1" type of
column-number shortcut. This provides useful functionality for
grouping/ordering, but not so much for HAVING. As in the OP's
example "HAVING f1 <> 0", you'd not be able to provide what is asked
for unless you allow the aliases to be buried within expressions.
And at that point you have got a really serious problem with ambiguity
against column names that are exposed by the FROM clause.

The long and the short of it is that allowing aliases in GROUP BY at all
was a mistake, and we're not going to enlarge that mistake by the amount
that would be needed to do what the OP asks.

regards, tom lane

In response to

Browse pgsql-general by date

  From Date Subject
Next Message mailtolouis2020-postgres 2008-05-14 16:07:03 postgres crash when select a record
Previous Message Decibel! 2008-05-14 16:02:37 Re: Best approach for large table maintenance