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.
Mysql has likely taught you some bad habits, like selecting fields
that aren't unique / grouped by, which is not legal in a grouped
query.
> I am wondering if I am missing something because this looks like my
> expression (id % 3) is getting evaluated twice when it really only
> should be done once. Now, this query is obviously watered down from
> what I am really doing and the expression is fairly lengthy and
> complex so I would prefer to not have to evaluate it more times than
> necessary.
Please come up with a test case query that really shows what you're
trying to do, the edited query is not workable, and I'm not sure which
errors you're introducing now or when you first wrote the query.