Re: HAVING confusion..

"George Henry C. Daswani" <gdaswani@Vorlon.odc.net> writes:
> Basically I do
> SELECT u_id,expire_date FROM users HAVING expire_date >= (NOW())
> expire_date is of timestamp data type.
> and it keeps failing with
> ERROR:  This is not a valid having query!

It's right: that isn't.  You should be using WHERE not HAVING.

WHERE is for selecting individual rows to include or exclude.  HAVING is
for selecting groups to list or not list when you have done a grouped
query (GROUP BY).  That is, WHERE filters out rows before they are
collected into groups, and HAVING applies to the resulting groups.
So, HAVING without GROUP BY is incorrect.

I agree the error message could be a little more specific...

> basically I want to do the above that works with great with mysql

Evidently mysql is lax about the difference between WHERE and HAVING.
I wonder whether they get it right when you do have a GROUP BY clause?

            regards, tom lane