HAVING clause working in postgres 8.0, but not in 8.2
Hello all,
We have recently upgrade our postgres server from 8.0 to 8.2. I am
experiencing some difficulties in SQL queries.
Let's say I have a table NUMBERS (number (integer)) which has values
1, 5 and 8.
SELECT number FROM numbers; will return 1, 5 and 8.
In PostgreSql 8.0, SELECT number FROM numbers HAVING number = 5;
returns 5
But in 8.2, it gives me the following error: ERROR: column
"numbers.number" must appear in the GROUP BY clause or be used in an
aggregate function.
In the documentation, it says that HAVING can be used without GROUP BY
or aggregate functions.
Has anybody already experienced this, is this a bug or am I missing
something?
TIA,
Vladimir
ujkavlade@gmail.com writes:
In PostgreSql 8.0, SELECT number FROM numbers HAVING number = 5;
returns 5
But in 8.2, it gives me the following error: ERROR: column
"numbers.number" must appear in the GROUP BY clause or be used in an
aggregate function.
In the documentation, it says that HAVING can be used without GROUP BY
or aggregate functions.
It can; whether it's useful or not is another question.
Per the 8.1 release notes:
* Fix HAVING without any aggregate functions or GROUP BY so that the
query returns a single group
Previously, such a case would treat the HAVING clause the same as
a WHERE clause. This was not per spec.
The above query is incorrect because it hasn't done anything to create
a grouped column.
regards, tom lane