Fix "division by zero" in PostgreSQL
ERROR: division by zero
One of the rows in your query divides by 0, and Postgres stops the whole query instead of guessing an answer. Usually it's a ratio where some group has nothing in the bottom part, like a user with no credits or a buyer with no visits.
Why it happens
- A ratio where some groups have a zero total or a zero count in the denominator.
- SUM(CASE ... ELSE 0 END) or COUNT(*) FILTER (...) as a divisor. Both return 0, not NULL, when nothing matches.
- Growth rates where the previous period was 0.
How to fix it
1. Wrap the divisor in NULLIF
Fails
SELECT user_id, SUM(CASE WHEN entry_type = 'debit' THEN amount ELSE 0 END) / SUM(CASE WHEN entry_type = 'credit' THEN amount ELSE 0 END) AS spend_ratio FROM wallet_ledger GROUP BY user_id;
Works
SELECT user_id, ROUND(SUM(CASE WHEN entry_type = 'debit' THEN amount ELSE 0 END) / NULLIF(SUM(CASE WHEN entry_type = 'credit' THEN amount ELSE 0 END), 0), 2) AS spend_ratio FROM wallet_ledger GROUP BY user_id ORDER BY user_id;
User 6 has a debit but no credits, so the bottom is 0. NULLIF(x, 0) returns NULL when x is 0, and anything divided by NULL is NULL, so that user gets an empty ratio instead of an error.
2. Pick a fallback value for zero counts
Fails
SELECT buyer_id, COUNT(*) FILTER (WHERE outcome = 'no_show') / COUNT(*) FILTER (WHERE outcome = 'attended') AS no_shows_per_visit FROM home_viewings GROUP BY buyer_id;
Works
SELECT buyer_id, COALESCE(ROUND(COUNT(*) FILTER (WHERE outcome = 'no_show')::numeric / NULLIF(COUNT(*) FILTER (WHERE outcome = 'attended'), 0), 2), 0) AS no_shows_per_visit FROM home_viewings GROUP BY buyer_id ORDER BY buyer_id;
Buyer 3 never attended, so the count is 0. Decide what that should show: NULL (leave out COALESCE) or a value like 0. The ::numeric also avoids integer division, which would turn 1/2 into 0.
Try it yourself
This is the broken query, running on real PostgreSQL in your browser. Press Run to see the error explained, then fix it.
Starting Postgres in your browser…
Real Postgres in your browser. Edit the query and run it, or open it in the full playground.
Practice so it doesn't happen in an interview
- Wallet balance from a ledgerSUM(CASE) · signed amounts · LEFT JOIN + COALESCE
- No-show rate without test accountsConditional ratio · JOIN filter · FILTER
- Refund rate per customerConditional aggregation · FILTER
- Month-over-month growthCTE + LAG · growth rate