Re: Alias in the HAVING clause

On Tue, May 13, 2008 at 4:18 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Tue, May 13, 2008 at 3:43 PM, Nathan Thatcher <n8thatcher@gmail.com> wrote:
>> I am in the middle of switching a bunch of queries over from MySQL to
>> PostgreSQL and have hit a little snag. The following query works fine
>> in MySQL but raises an error in postgres:
>>
>> SELECT COUNT(*), id % 3 AS f1 FROM table GROUP BY f1 HAVING f1 <> 0;
>>
>> It seems that Postgres does not recognize the alias in the HAVING
>> clause. Rewriting it like this works in postgres:
>>
>> SELECT COUNT(*), id % 3 AS f1 FROM table GROUP BY f1 HAVING
event_id % 3 <> 0;
>
> I think you're editing your queries to show to us.  There's no way
> that query would run, as you're selecting id and grouping by f1.  f1
> doesn't exist at the time the group by fires.  Proper execution
> sequence is:  where clause, group by, select list, having clause.

Nevermind, I misunderstood which error I was getting there.  Still,
while it's nice to simplify queries for posting, it's a good idea to
create a test example.  i.e.

create table...
insert into...
select ...

to show the problem so it's easy to reproduce.  I wouldn't worry about
the possible double re-evaluation, it's no big loss.  As for the
having clause, I'm pretty sure it can only operate on real columns,
not aliases.