PostgreSQL error: division by zero

SQLSTATE 22012 (division_by_zero) · Updated July 9, 2026

ERROR:  division by zero

Some division (or modulo) in the query hit a zero denominator. Unlike some databases and most floating-point environments, PostgreSQL raises an error rather than returning NULL or infinity — so any division whose denominator can be zero needs to say what should happen.

What this error means

The error aborts the whole statement (and the transaction), even if only one row out of millions had the zero. That is why it typically appears in aggregations and reports: everything works until the first day a group is empty or a total is zero. The fix is almost always the same idiom, applied at the division site.

Common causes

How to diagnose it

Find the divisions in the query and ask which denominator can be zero. To see the offending rows, select the denominator expression with its grouping keys and filter for zero — the rows that come back are the ones that will explode the division.

How to fix it

-- L'idioma standard: denominatore zero → risultato NULL
SELECT clicks / NULLIF(impressions, 0) AS ctr FROM stats;

-- Se vuoi 0 (o altro default) invece di NULL:
SELECT COALESCE(clicks / NULLIF(impressions, 0), 0) AS ctr FROM stats;