EC
22012
PostgreSQLERRORNotableData ExceptionHIGH confidence

division by zero

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
  1. 1Dividing a numeric, integer, or floating-point value by zero in SQL or PL/pgSQL
  2. 2Using modulo (%) with a zero divisor
  3. 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
trigger — this will error
SELECT revenue / expenses FROM departments;
-- fails when expenses = 0

expected output

ERROR:  division by zero

Fix 1

Use NULLIF to prevent zero division

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

Use NULLIF to prevent zero division
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 When a specific default (e.g., 0 or 100) should replace the division result.

Use CASE to return a default value
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

Zero expenses may indicate data quality issues that should be investigated and fixed at the source.

Sources
Official documentation ↗

Class 22 — Data Exception

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All PostgreSQL errors