Thread: HAVING confusion..

Hello,

    I'm using postgresql 6.4.2 and i'm having a problem with the
"HAVING" clause..


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!

even doing something simple like
SELECT u_id from users having u_id > 5

fails with the same error..

select sell_by_date,item_num FROM sell,users,i
tem WHERE sold_by = u_id AND sell_item = item_num AND category_id =
'$parameter'
 ORDER BY register_date DESC HAVING sell_by_date >= (NOW())

basically I want to do the above that works with great with mysql
, solid and etc..

"George Henry C. Daswani" wrote:
  >Hello,
  >
  >    I'm using postgresql 6.4.2 and i'm having a problem with the
  >"HAVING" clause..
  >
  >
  >Basically I do
  >
  >SELECT u_id,expire_date FROM users HAVING expire_date >= (NOW())
...
  >and it keeps failing with
  >ERROR:  This is not a valid having query!

You should only use HAVING with GROUP BY; for what you are doing, you
just want WHERE.

SELECT u_id,expire_date FROM users
   WHERE expire_date >= date(now());
u_id |expire_date
-----+-----------
fred | 21-03-1999
tom  | 01-05-1999
harry| 01-05-1999
sarah| 21-03-1999
dick | 12-04-1999
(5 rows)

SELECT expire_date,COUNT(*) FROM users
   GROUP BY expire_date
   HAVING expire_date >= date(now());
expire_date|count
-----------+-----
 21-03-1999|    2
 12-04-1999|    1
 01-05-1999|    2
(3 rows)

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
               PGP key from public servers; key ID 32B8FAA1
                 ========================================
     "The LORD bless thee, and keep thee; The LORD make his
      face shine upon thee, and be gracious unto thee; The
      LORD lift up his countenance upon thee, and give thee
      peace."              Numbers 6:24-26

"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