division by zero" in SQL: NULLIF pattern

TL;DR

Wrap the denominator in NULLIF(denominator, 0): when it is zero the division yields NULL instead of raising. Then decide what NULL means downstream, usually with COALESCE(..., 0). It happens because SQL has no infinity for integer division; zero denominators are an error, not a value.

ERROR: division by zero

Use this when

• A query raises division by zero
• Computing ratios, percentages, or rates over columns that can be zero
• You want NULL (unknown) instead of a crash for the zero case

Not for

• Integer division truncation (5/2 = 2), cast to numeric instead
• General NULL handling, separate topic
• Division errors in Python/pandas code

Steps

1. Find the zero denominators:

SELECT COUNT(*) FROM metrics WHERE denominator = 0;

Expected output: the count of rows that would crash the division.

2. Guard with NULLIF:

SELECT numerator / NULLIF(denominator, 0) AS ratio
FROM metrics;

Expected output: the ratio where defined, NULL where the denominator was zero. No error.

3. Decide the business meaning of the zero case:

SELECT COALESCE(numerator / NULLIF(denominator, 0), 0) AS ratio
FROM metrics;

Expected output: 0 instead of NULL for zero denominators. Only do this if 0 is actually the right answer (often it isnt; NULL is more honest).

4. For percentages, multiply after guarding:

SELECT 100.0 * numerator / NULLIF(denominator, 0) AS pct
FROM metrics;

Expected output: a proper percentage. The 100.0 forces non-integer division too.

Variant phrasings

postgres division by zero error

NULLIF is the standard fix. Alternatives like CASE WHEN work but are wordier.

safe divide sql

Some dialects have SAFE_DIVIDE (BigQuery) or DIV0 (Snowflake); NULLIF is the portable version.

nullif vs case when denominator zero

NULLIF(d, 0) is shorthand for CASE WHEN d = 0 THEN NULL ELSE d END. Same result, less noise.

Why it happens

SQL treats division by zero as an error, not as infinity, because the standard says so and because silently returning infinity would corrupt aggregates. NULLIF converts the problematic zero into NULL, and any arithmetic with NULL yields NULL instead of raising, which is the language's way of saying "unknown".

Edge cases

• NULL denominators already yield NULL; NULLIF only handles the zero case.
• Integer division: 5 / NULLIF(2, 0) is still integer math; cast one side to numeric for decimals.
• Aggregates ignore NULLs, so AVG(x / NULLIF(d, 0)) silently drops the zero rows; make sure that is what you want.
• In WHERE clauses the NULL result filters the row out; that is usually correct for ratios.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst1Ykf608fpwIlC263_9PCg