division by zero

Category: Data ExceptionVersions: All Postgres versions

What this means

SQLSTATE 22012 is raised when an arithmetic expression attempts to divide a number by zero, or when a modulo operation has a zero divisor. The statement is aborted.

Why it happens

1Dividing a numeric, integer, or floating-point value by zero in SQL or PL/pgSQL
2Using modulo (%) with a zero divisor
3A divisor column or expression evaluates to zero for some rows in a query

How to reproduce

Dividing a column value by another column that contains zero.

trigger — this will ERROR

SELECT revenue / expenses FROM departments;
-- fails when expenses = 0

ERROR: division by zero

Fix 1: Use NULLIF to prevent zero division

When the divisor may be zero and NULL is an acceptable result.

fix

SELECT revenue / NULLIF(expenses, 0) FROM departments;

Why this works

NULLIF(expenses, 0) returns NULL when expenses is 0. Division by NULL produces NULL rather than an error.

Fix 2: Use CASE to return a default value

When a specific default (e.g., 0 or 100) should replace the division result.

fix

SELECT CASE WHEN expenses = 0 THEN 0 ELSE revenue / expenses END
FROM departments;

Why this works

The CASE avoids evaluating the division branch when the divisor is zero.

What not to do

✗

Filter out zero-divisor rows without understanding why they exist

Why it's wrong: Zero expenses may indicate data quality issues that should be investigated and fixed at the source.

Sources

📚 Official docs: https://www.postgresql.org/docs/current/errcodes-appendix.html

🔧 Source ref: Class 22 — Data Exception

Confidence assessment

✅ HIGH confidence

Standard SQLSTATE. Behaviour consistent across all Postgres versions. NULLIF approach is canonical.