PostgreSQL error 42803 occurs when you attempt to nest one aggregate function (like COUNT, SUM, AVG) directly inside another aggregate function. Use subqueries, CTEs, or window functions to break the nesting into multiple query levels.
Error 42803 ("Aggregate function calls cannot be nested") occurs when PostgreSQL encounters an aggregate function call inside the parameter list of another aggregate function at the same query level. PostgreSQL's query execution model processes aggregates in a single pass per query level, making it impossible to nest them directly. For example, you cannot write MAX(COUNT(*)) or AVG(SUM(price)) in a single SELECT clause. This restriction exists because aggregate functions operate on sets of rows and produce a single result value. Nesting them would create ambiguity about which rows to aggregate first. PostgreSQL resolves this by prohibiting direct nesting and requiring you to explicitly separate the aggregation levels using subqueries, Common Table Expressions (CTEs), or window functions.
First, locate the problematic query. It will have one aggregate function nested inside another:
-- This fails:
SELECT MAX(COUNT(*)) FROM orders GROUP BY customer_id;
-- Or:
SELECT AVG(SUM(amount)) FROM sales GROUP BY product_id;Wrap the inner aggregate in a subquery, then apply the outer aggregate to the subquery result:
-- Fixed with subquery:
SELECT MAX(order_count)
FROM (
SELECT COUNT(*) as order_count
FROM orders
GROUP BY customer_id
) subquery;The key is that the inner aggregate (COUNT) completes in the subquery, producing one row per customer. The outer MAX then aggregates those results.
CTEs can be clearer and more readable than nested subqueries:
WITH customer_totals AS (
SELECT customer_id, SUM(amount) as total_amount
FROM orders
GROUP BY customer_id
)
SELECT AVG(total_amount) as avg_customer_spending
FROM customer_totals;This separates the two aggregation steps clearly: first compute per-customer totals, then compute the average across customers.
If you need both the aggregate value and row-level detail, use window functions instead:
SELECT
customer_id,
order_id,
amount,
SUM(amount) OVER (PARTITION BY customer_id) as customer_total,
AVG(SUM(amount) OVER (PARTITION BY customer_id)) OVER () as avg_customer_total
FROM orders;Window functions avoid aggregation altogether by computing results "alongside" the original rows, eliminating the need for nested aggregates.
If nesting JSONB_AGG, ARRAY_AGG, or STRING_AGG within other aggregates, apply the same subquery pattern:
-- This fails:
SELECT COUNT(DISTINCT jsonb_agg(tags))...
-- Use subquery:
SELECT COUNT(DISTINCT tag_group)
FROM (
SELECT jsonb_agg(tags) as tag_group
FROM products
GROUP BY category
) sub;Why PostgreSQL Prohibits Nested Aggregates: PostgreSQL parses and executes aggregate functions in a single pass per query level. The FROM clause defines the row set, GROUP BY partitions it, and each aggregate in the SELECT list processes its partition independently. Nesting aggregates would require PostgreSQL to keep multiple aggregation states in memory simultaneously or perform multiple passes, complicating the executor. This design choice improves performance and query clarity. In some OLAP databases or analytical tools, nested aggregates are allowed because they use different execution strategies (e.g., hierarchical processing). PostgreSQL's approach is more conservative and focuses on clarity. If you find yourself writing deeply nested aggregates, consider whether window functions, CTEs, or denormalization might be better solutions. For very complex analytical queries, migrating to a specialized OLAP database may be necessary, but for typical SQL operations, the subquery/CTE pattern is clear and performant.
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL