SQL Avoid Division by Zero

Dividing by zero raises a genuine error in Postgres, not a NULL or an infinity — this closing lesson covers the standard guard using NULLIF.

What & Why

Unlike some languages, Postgres genuinely errors on division by zero — the query fails outright rather than returning a special "infinity" value. Any percentage or ratio calculation whose denominator could legitimately be zero needs a guard, and NULLIF (from the NULL Handling series) is the standard tool: convert a zero denominator into NULL first, since dividing by NULL safely returns NULL instead of erroring.

See How It Works

BUSINESS QUESTION

Marketing wants to compare the failure an unguarded bounce-rate calculation would cause with the safe NULLIF result.

id	campaign_id	sent_at	recipients	opens	clicks	unsubscribes	bounces
201	1	2024-01-20 15:00:00+00	1200	540	180	9	24
202	2	2024-02-15 16:30:00+00	800	420	96	5	12
203	3	2024-03-18 13:00:00+00	1500	610	225	14	31
204	4	2024-04-08 14:00:00+00	0	0	0	0	0

Compare unguarded division with the NULLIF resultExample 1 of 4

unguarded bounces / recipients · guarded bounces / NULLIF(recipients, 0)

id	recipients	bounces	unguarded_behavior	guarded_bounce_rate_pct
201	1200	24	2.00	2.00
202	800	12	?	?
203	1500	31	?	?
204	0	0	?	?

With 1,200 recipients, the unguarded and guarded calculations agree at 2.00 percent.

EXAMPLE QUERY

SELECT
  id,
  recipients,
  bounces,
  ROUND(bounces::numeric / NULLIF(recipients, 0) * 100, 2) AS guarded_bounce_rate_pct
FROM marketing.email_sends
ORDER BY id;

Now You Try

Practice this concept

Marketing wants an executable bounce-rate query even when an email send has zero recipients.

Available schema

marketing

Prefix tables with marketing.table_name.

idrecipientsbouncesguarded_bounce_rate_pct

marketing.email_sends

Column	Type
id	integer
campaign_id	integer
sent_at	timestamp with time zone
recipients	integer
opens	integer
clicks	integer
unsubscribes	integer
bounces	integer
legacy_send_group	text

query.sql

Intermediate business practice