Re: HAVING and column alias

Mike Mascari <mascarm@mascari.com> writes:
> SELECT
> SUM(p.qty),
> (SELECT date_trunc('day', sales.active)
>   FROM sales
>   WHERE sales.purchase = p.purchase) AS field1
> FROM purchases p
> GROUP BY field1
> HAVING (field1 IS NOT NULL);

> ERROR: Attribute 'field1' not found

This is definitely illegal per the SQL spec: output column names are not
legal per spec in either GROUP BY or HAVING.  Postgres is lax about this
in GROUP BY (mainly for historical reasons), but not in HAVING --- and
even in GROUP BY, we only recognize an output column name if it is used
by itself, not as part of an expression.  So your HAVING clause would
lose even if we applied GROUP-BY-like rules to it.

If you can't restructure the query, I think you'll have to repeat the
sub-SELECT in the HAVING clause rather than refer to it via the field1
alias.  If you can restructure, consider

SELECT * FROM
(SELECT
 SUM(p.qty),
 (SELECT date_trunc('day', sales.active)
   FROM sales
   WHERE sales.purchase = p.purchase) AS field1
 FROM purchases p
 GROUP BY field1) ss
WHERE (field1 IS NOT NULL);

            regards, tom lane