Re: Column aliases for GROUP BY and HAVING

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: Column aliases for GROUP BY and HAVING
Дата
Msg-id 7608.1259177709@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Column aliases for GROUP BY and HAVING  (Rikard Bosnjakovic <rikard.bosnjakovic@gmail.com>)
Ответы Re: Column aliases for GROUP BY and HAVING  (Rikard Bosnjakovic <rikard.bosnjakovic@gmail.com>)
Список pgsql-novice
Rikard Bosnjakovic <rikard.bosnjakovic@gmail.com> writes:
> Why isn't it possible to refer to a column alias in HAVING?

According to the SQL standard you aren't allowed to refer to an output
column alias in *any* of those clauses.  It's nonsensical because the
output columns aren't (logically speaking) computed until after the
GROUP BY/HAVING computations have been done.  For instance, you'd
probably not be happy if this failed with a zero-divide error:

    SELECT 1/x, avg(y) FROM tab GROUP BY x HAVING x <> 0;

In practice PG allows you to refer to output column aliases as simple
GROUP BY and ORDER BY entries, though not as part of expressions.
This is historical rather than something we'd be likely to do if we
were starting over, though I admit it does save typing in a lot of
cases.

HAVING is not included because (a) it wasn't historically, and (b)
the use-case for a bare column alias in HAVING would be pretty small
anyway.  Your example wouldn't work even if HAVING acted the same
as GROUP BY/ORDER BY, since you didn't just write the alias but
tried to compare it to something else.

            regards, tom lane

В списке pgsql-novice по дате отправления:

Предыдущее
От: Rikard Bosnjakovic
Дата:
Сообщение: Column aliases for GROUP BY and HAVING
Следующее
От: Joshua Tolley
Дата:
Сообщение: Re: Anonymous code blocks