Division By Zero In Rate Calculation
A previously reliable rate/ratio query or UPDATE suddenly fails with 22012 division_by_zero. The failure is all-or-nothing: because one statement computes...
In 10 seconds
A scheduled job recomputes a click-through rate for every campaign with ctr = clicks / impressions. It ran fine for months, then started failing overnight with 'ERROR: division by zero'. A handful of campaigns now report zero impressions, and the very first one the UPDATE touches aborts the entire statement -- so no rate is written for any campaign and the report is stale. The fix is to guard the divisor, but the same diagnostics expose something worse: some of those zero-impression rows still have clicks, which is impossible and points at an upstream tracking bug.
Why This Happens
Division in SQL is a strict operator: x / 0 is undefined, so PostgreSQL raises 22012 rather than inventing a value. The query divides clicks by impressions for all 1000 rows in a single UPDATE; when it reaches the first row with impressions = 0 the operator raises, the statement's transaction rolls back, and every row is left unchanged -- including the 995 rows that would have divided cleanly. The presence of even one zero in the denominator is enough to break the whole batch.
Diagnose
Free Tier -- Basic Approach
Size the problem with one count: how many rows have a zero divisor. This is the number of rows the division cannot handle.
SELECT count(*) AS zero_divisor_rows FROM campaign_stats WHERE impressions = 0;
zero_divisor_rows
-------------------
5
(1 row)
SELECT count(*) AS zero_divisor_rows FROM campaign_stats WHERE impressions = 0. Five rows have no impressions -- enough to abort the whole UPDATE even though 995 rows are fine.
Fix
Guard the divisor with NULLIF(impressions, 0). A zero divisor becomes NULL, the division yields NULL for those rows, and every other row computes normally -- so the single UPDATE now touches all 1000 rows instead of aborting.
UPDATE campaign_stats SET ctr = clicks::numeric / NULLIF(impressions, 0);
UPDATE 1000
UPDATE campaign_stats SET ctr = clicks::numeric / NULLIF(impressions, 0). The statement completes with UPDATE 1000: the 995 real rows get their rate, and the 5 zero-impression rows get NULL (an honest 'undefined' rather than a crash or a fake 0).
Verify
Confirm three things: every row was processed, the zero-impression rows carry NULL (rate undefined, not a wrong number), and a bare 1/0 still raises -- proving the fix is the NULLIF guard, not a disabled check.
SELECT count(*) AS rows_with_any_value,
count(*) FILTER (WHERE ctr IS NULL) AS null_ctr_rows
FROM campaign_stats;
rows_with_any_value | null_ctr_rows
---------------------+---------------
1000 | 5
(1 row)
SELECT id, ctr FROM campaign_stats WHERE impressions = 0 ORDER BY id;
id | ctr ------+----- 996 | 997 | 998 | 999 | 1000 | (5 rows)
SELECT 1 / 0;
ERROR: division by zero
SELECT count(*) AS rows_with_any_value, count(*) FILTER (WHERE ctr IS NULL) AS null_ctr_rows -- expect 1000 and 5. List the zero-impression rows to see their ctr is NULL.
Finally SELECT 1 / 0 still returns division by zero, confirming the operator is unchanged and only the guarded expression was made safe.
⏪ Rollback
The broken UPDATE changes nothing -- it aborts before committing, so the ctr column stays exactly as it was (all NULL here) and there is nothing to undo. The fix UPDATE that uses NULLIF is a normal, fully reversible write: if you want the column empty again you can run UPDATE campaign_stats SET ctr = NULL. No schema change, no DDL, no lock beyond the row locks of a single UPDATE.
📦 Version Matrix (PG 14--18)
Run the broken division and the NULLIF-guarded fix on PostgreSQL 14 through 18. Every version raises the same division by zero and accepts the same guarded UPDATE.
| Version | Behavior | Version Notes |
|---|---|---|
PG 14.23 |
broken=division by zero | ✗ Not available -- use stats_reset age |
PG 15.18 |
broken=division by zero | ✗ Not available -- use stats_reset age |
PG 16.14 |
broken=division by zero | ✗ Not available -- use stats_reset age |
PG 17.10 |
broken=division by zero | ✗ Not available -- use stats_reset age |
PG 18.4 |
broken=division by zero | ✗ Not available -- use stats_reset age |
⚠ Common Mistakes
Stop this incident before it pages you
The free runbook clears today's failure. Pro adds the standing guardrails -- the alert query, the dashboard, and the managed-service knobs -- so it never becomes an incident again.
- ✓ A ready-to-paste alert query with the right thresholds.
- ✓ The topology change that prevents recurrence.
- ✓ The exact knobs on RDS, Azure Flexible Server & Cloud SQL.
Frequently Asked Questions
Why does one zero divisor break the rate for every campaign, not just the bad ones?
The job computes the rate for all rows in a single UPDATE. SQL statements are atomic: when the division operator raises 22012 on the first impressions = 0 row, the entire statement rolls back, so none of the 995 good rows get their ctr either. The blast radius is the whole UPDATE, which is why the report goes fully stale.
Should I use NULLIF, COALESCE, or a CASE expression?
Use NULLIF(divisor, 0): it turns a 0 divisor into NULL so the division yields NULL instead of raising. COALESCE goes the wrong way (it would replace NULL with 0 and re-trigger the error). A CASE WHEN impressions = 0 THEN NULL ELSE clicks/impressions END is equivalent but longer; NULLIF is the idiomatic one-liner.
Is NULL the right answer for a zero-impression rate?
Yes -- a rate with no denominator is genuinely undefined, and NULL says exactly that. Forcing it to 0 would imply a real 0% click-through, which is misleading. But NULL also hides the data quirk, so keep a separate guard that counts impossible rows (clicks present with zero impressions) so the underlying bug still gets noticed.
Does this differ between integer and numeric division?
The result type differs (integer division truncates; numeric keeps the fraction), but the zero-divisor rule is identical -- both raise 22012, just from different internal routines (int4div vs div_var). The NULLIF guard works the same way for both.