From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-sql@hub.org
Subject: Re: HAVING confusion..
Date: Mon, 22 Feb 1999 10:35:44 -0500
Message-ID: <8694.919697744@sss.pgh.pa.us> (raw)
In-Reply-To: <199902220400.XAA82447@hub.org>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, pgsql-sql@hub.org
Subject: Re: HAVING confusion..
In-Reply-To: <8694.919697744@sss.pgh.pa.us>
References: <199902220400.XAA82447@hub.org> <8694.919697744@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox