PostgreSQL doesn't allow aggregate functions (COUNT, SUM, AVG, MAX, MIN) directly in the WHERE clause. This happens because WHERE filters rows before aggregation is computed. Use the HAVING clause instead to filter aggregated results.
This error occurs when you attempt to use an aggregate function like COUNT(), SUM(), AVG(), MAX(), or MIN() directly in a WHERE clause. PostgreSQL evaluates the WHERE clause before any grouping or aggregation takes place, so aggregate functions haven't been computed yet at that stage. The WHERE clause is meant for filtering individual rows, while aggregate functions operate on groups of rows.
WHERE filters individual rows BEFORE grouping and aggregation. HAVING filters grouped rows AFTER aggregation is computed. Since WHERE is evaluated first, it cannot reference aggregate functions.
Convert your query to use HAVING instead of WHERE for aggregate conditions:
-- WRONG: Aggregate function in WHERE
SELECT category, COUNT(*) as count
FROM products
WHERE COUNT(*) > 5
GROUP BY category;
-- CORRECT: Aggregate function in HAVING
SELECT category, COUNT(*) as count
FROM products
GROUP BY category
HAVING COUNT(*) > 5;For more complex scenarios where you need to filter based on aggregate results without grouping, use a subquery:
-- Find the city with the maximum temperature
SELECT city, temp
FROM weather
WHERE temp = (SELECT MAX(temp_lo) FROM weather);
-- Find orders where total exceeds the average
SELECT order_id, total
FROM orders
WHERE total > (SELECT AVG(total) FROM orders);Combine WHERE and HAVING clauses properly:
-- Filter individual rows with WHERE, then aggregate with GROUP BY,
-- then filter aggregated results with HAVING
SELECT department, AVG(salary) as avg_salary
FROM employees
WHERE hire_date > '2020-01-01' -- Filter rows before aggregation
GROUP BY department
HAVING AVG(salary) > 50000; -- Filter groups after aggregationThe SQL standard specifies that WHERE is evaluated before GROUP BY and aggregate functions are computed. This is why PostgreSQL (and most SQL databases) disallow aggregate functions in WHERE. If you need to filter based on aggregates of subsets, consider using Common Table Expressions (CTEs) with WITH clauses for better readability. Window functions (OVER clause) can also provide alternatives for some use cases where you need row-by-row comparisons with aggregate values.
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL
ERROR 42501: must be owner of table
How to fix "must be owner of table" in PostgreSQL
trigger cannot change partition destination
How to fix "Trigger cannot change partition destination" in PostgreSQL
SSL error: certificate does not match host name
SSL error: certificate does not match host name in PostgreSQL
No SSL connection
No SSL connection to PostgreSQL