On Tue, May 13, 2008 at 4:18 PM, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote: > On Tue, May 13, 2008 at 3:43 PM, Nathan Thatcher <n8thatcher@xxxxxxxxx> 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.
