Menu

PostgreSQL Error 42803: Grouping Error

Fix PostgreSQL SQLSTATE 42803 by grouping selected columns correctly, aggregating them, or using the supported primary-key dependency.

Posted on By
On this page

PostgreSQL SQLSTATE 42803 is grouping_error. It commonly appears when a query uses GROUP BY or an aggregate but selects a column that is neither grouped nor aggregated. See the PostgreSQL 18 error-code appendix.

Find the ungrouped column

For each selected expression, check whether it is a grouping expression or an aggregate. For example, this query selects payment_id but groups rows only by customer_id:

SELECT customer_id, payment_id, SUM(amount)
FROM public.payment
GROUP BY customer_id;

If a customer has multiple payments, there is no single payment_id value PostgreSQL can return for that customer group. Choose the result you actually need:

  • Return one row per customer and remove payment_id from the SELECT list.
  • Return one row per customer and payment by grouping by both customer_id and payment_id.
  • If one payment value is meaningful for the group, use an appropriate aggregate such as MIN(payment_id) or MAX(payment_id); do not use an aggregate merely to hide the error.

Check functional dependency on a primary key

PostgreSQL permits selecting an ungrouped column when the grouped columns include the primary key of the table containing that column. For example, when customer_id is the primary key of customer, its other columns are determined by that key:

SELECT c.customer_id, c.first_name, COUNT(p.payment_id) AS payment_count
FROM public.customer AS c
LEFT JOIN public.payment AS p ON p.customer_id = c.customer_id
GROUP BY c.customer_id;

This exception is based on a primary-key dependency; it does not apply to every unique-looking expression or arbitrary join. When in doubt, group by the intended columns explicitly or aggregate the values. See PostgreSQL’s SELECT and GROUP BY rules.

Handle HAVING and aggregate filters

Use WHERE to filter input rows before grouping and HAVING to filter groups after aggregation. A non-aggregated column used in HAVING must also be grouped or functionally dependent on the grouping key. For example:

SELECT customer_id, SUM(amount) AS total_amount
FROM public.payment
GROUP BY customer_id
HAVING SUM(amount) > 100;

If you meant to filter individual payments, move that condition to WHERE. See the PostgreSQL GROUP BY tutorial and HAVING tutorial.

Distinguish a grouping error from a type mismatch

42803 is about an ungrouped expression in an aggregate query. 42804 (datatype_mismatch) means PostgreSQL could not reconcile types in an assignment or expression; see Error 42804 troubleshooting.

For more SQLSTATE guides, browse PostgreSQL Error Troubleshooting.