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
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 / orderswhere some rows have zero orders. - An average computed by hand as
sum / countover an empty group. - Relying on a
WHEREorCASEelsewhere in the query to filter zero rows out before the division runs.
How to fix it
-
Turn a zero divisor into
NULLwithNULLIF; the result is thenNULLinstead of an error.SELECT region, revenue / NULLIF(order_count, 0) AS avg_order FROM region_totals;
-
Use
CASEwhen zero should produce a specific value.SELECT CASE WHEN order_count = 0 THEN 0 ELSE revenue / order_count END FROM region_totals;
-
For averages, use
avg(), which ignoresNULLand returnsNULLfor 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
22003numeric value out of range25P02current transaction is aborted8134SQL Server: Divide by zero error encounteredORA-01476Oracle: divisor is equal to zero
More data exception errors
22021invalid byte sequence for encoding22008date/time field value out of range22007invalid datetime format22023invalid parameter value22001value too long for type22P02invalid input syntax for type
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.
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.