// PostgreSQL error reference

PostgreSQL 22012: division by zero

An expression divided a number by zero. PostgreSQL raises an error instead of returning NULL or infinity for integer and numeric division.

SQLSTATE
22012
Name
division_by_zero
Category
Data Exception
Error message
division by zero

What it means

Dividing an integer or numeric value by zero, or taking a modulo by zero, is an error in PostgreSQL. It usually appears in reports and ratios where some groups have a zero denominator. Floating-point division by zero is an error too.

Because PostgreSQL may evaluate expressions in any order, a WHERE x <> 0 filter does not always protect an expression in the select list of the same query; guard the expression itself.

Common causes

  • A ratio such as revenue / orders where some rows have zero orders.
  • An average computed by hand as sum / count over an empty group.
  • Relying on a WHERE or CASE elsewhere in the query to filter zero rows out before the division runs.

How to fix it

  1. Turn a zero divisor into NULL with NULLIF; the result is then NULL instead of an error.

    SELECT region, revenue / NULLIF(order_count, 0) AS avg_order
    FROM region_totals;
  2. Use CASE when zero should produce a specific value.

    SELECT CASE WHEN order_count = 0 THEN 0 ELSE revenue / order_count END
    FROM region_totals;
  3. For averages, use avg(), which ignores NULL and returns NULL for an empty set.

    SELECT customer_id, avg(amount) FROM payments GROUP BY customer_id;

Reproduce it safely

SELECT 1 / 0;  -- ERROR: division by zero

Related errors

More data exception errors

The code and message text are from the PostgreSQL documentation, Appendix A: PostgreSQL Error Codes. Browse the full PostgreSQL error list or search every database error.

// fewer errors, faster fixes

Work on PostgreSQL in SQLly

A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for PostgreSQL and the other databases you use.