## 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.

```text
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:

```sql
SELECT COUNT(*) FROM metrics WHERE denominator = 0;
```
Expected output: the count of rows that would crash the division.

2. Guard with NULLIF:

```sql
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:

```sql
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:

```sql
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/pst_1Ykf60_8fpwIlC263_9PCg