Обсуждение: 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