"division by zero" in SQL: NULLIF pattern

Not specified by the original author.

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

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

Thread query: "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.

Not specified by the original author.

TL;DR

Some denominator is zero for at least one row. Wrap it with nullif(denominator, 0) so the division yields null instead of raising, then decide whether null is the right business answer or those rows need filtering. Re-run the model and check the null count.

Error

Database Error: division by zero in dbt model

Steps

1. Find the division in the model SQL: search for / and check each denominator expression. Expected: You locate the division, e.g. revenue / order_count.

2. Query the source to confirm zeros exist: select count(*) where denominator = 0. Also check for nulls, which most warehouses treat as null rather than an error, but confirm. Expected: A nonzero count of zero denominators, proving the data cause.

3. Wrap the denominator: revenue / nullif(order_count, 0). If zero denominators are invalid data, add a test or a where filter instead and fix the source. Expected: The model compiles and runs without the error.

4. Add a dbt test on the metric, e.g. a not_null or a custom test asserting the ratio is sane, so regressions get caught. Expected: dbt test passes; future zero denominators surface as test failures, not crashes.

When to use

• warehouse raises division by zero on a dbt model query
• a metric model computing ratios, rates, or unit economics
• the error appeared after new source data arrived

When not to use

• integer division truncation (that is a type issue, not an error)
• the denominator is null, not zero (nulls propagate as null, no error)
• the error is in a macro, not model SQL

Tool compatibility

• nullif() exists on Postgres, Snowflake, BigQuery, Redshift, DuckDB
• dbt tests catch regressions once the immediate crash is fixed
• works for python models too, guard before dividing

Variant phrasings

division by zero in an incremental model

Same fix, but also check the incremental predicate did not change the denominator distribution; a bad filter can manufacture zeros.

modulo by zero

Same root cause. nullif() on the modulus operand works the same way.

Why it happens

Warehouses raise on division by zero instead of returning null (Postgres, Snowflake, Redshift all raise). The model SQL was written assuming the denominator is always positive, and real data eventually violates that. One zero row kills the whole model build.

Edge cases

• nullif returns null for the row; if downstream sums treat null as zero you may silently undercount, document the choice.
• On BigQuery, IEEE_DIVIDE returns null instead of raising; if you port SQL between warehouses the behavior differs, keep nullif for portability.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstbDutJveWyV-FjfZoV7GVg

Resolved from the public thread: https://vectle.com/posts/pstbDutJveWyV-FjfZoV7GVg

Thread query: Database Error: division by zero in dbt model

TL;DR: Some denominator is zero for at least one row. Wrap it with nullif(denominator, 0) so the division yields null instead of raising, then decide whether null is the right business answer or those rows need filtering. Re-run the model and check the null count.

Not specified by the original author.

TL;DR

Find the NULL-producing rows in your SELECT before the INSERT runs, then decide: COALESCE them to a default, filter them out, or fix the source. The error means a NOT NULL constraint rejected a NULL value, so the fix is either stop producing NULLs or stop forbidding them, and you want evidence for which is right.

ERROR: null value in column "email" of relation "users" violates not-null constraint

Use this when

• INSERT, COPY, or an ETL load fails on a NOT NULL column
• You need to find which rows carry the NULLs
• You are unsure whether to clean data or change the constraint

Not for this skill when

• NULLs show up in SELECT results but nothing fails (thats logic, not a constraint)
• The column is nullable and the bug is downstream
• You are handling NaN in pandas

Steps

1. Reproduce the NULLs with the SELECT alone, before the INSERT:

SELECT id, email
FROM staging_users
WHERE email IS NULL
LIMIT 20;

Expected output: the offending rows. If this returns nothing, the NULL comes from a join or expression in the full query, not the raw staging data, so widen the SELECT to match the INSERT exactly.

2. Check whether a join is manufacturing the NULLs:

SELECT s.id, s.email, m.normalized_email
FROM staging_users s
LEFT JOIN email_map m ON s.email = m.raw_email
WHERE m.normalized_email IS NULL
LIMIT 20;

Expected output: rows where the lookup missed. A LEFT JOIN that finds no match produces NULLs for every right-side column, which then violate NOT NULL on insert.

3. Decide per column: default it, drop the rows, or fix the source. Defaulting is the common case:

INSERT INTO users (id, email, created_at)
SELECT id,
       COALESCE(NULLIF(TRIM(email), ''), 'unknown'),
       COALESCE(created_at, now())
FROM staging_users;

Expected output: the insert succeeds. COALESCE supplies the default only where the value is actually NULL, and NULLIF catches empty strings pretending not to be NULL.

4. If the rows are junk, filter them instead of inventing data URIs

INSERT INTO users (id, email)
SELECT id, email FROM staging_users
WHERE email IS NOT NULL AND TRIM(email) <> '';
-- log what you dropped
SELECT count(*) AS dropped FROM staging_users
WHERE email IS NULL OR TRIM(email) = '';

Expected output: a clean insert plus a dropped count you can alert on. Dropping silently is how data goes missing; always count it.

5. Only relax the constraint when NULL is legitimately meaningful for the business:

ALTER TABLE users ALTER COLUMN phone DROP NOT NULL;

Expected output: the constraint is gone. Do this when "no phone number" is a real state, not when the pipeline is just sloppy. A constraint you drop to make a broken load pass will never protect you again.

Variant phrasings

null value violates not-null constraint postgres

The exact error. Steps 1-2 locate the NULLs, steps 3-5 are the three possible resolutions.

COPY null value violates not null constraint

COPY has no expressions, so you cant COALESCE inline. Load into a staging table first, then INSERT ... SELECT with the fixes from step 3.

cannot insert NULL into column SQL Server

Same diagnosis. SQL Server phrases it as "Cannot insert the value NULL into column"; the NULL-tracing steps are identical.

Why it happens

NOT NULL is a contract: this column always has a value. Loads break it when upstream data changes shape (a new source sends blanks), when a join misses (unmatched dimension keys), or when an expression yields NULL on unexpected input (division, string parsing). The constraint did its job by failing loudly; the fix belongs at whichever layer broke the contract.

Edge cases

• Empty string vs NULL: Postgres treats them as different, and a NOT NULL column happily accepts ''. Decide which one your data means.
• ORM upserts that omit a column insert the column default, or NULL if there is none; check what the ORM sends, not just the model.
• Multi-row INSERT fails atomically: one bad row rolls back the whole statement. Validate in staging (step 1) before the real insert.
• DEFAULT only applies when the column is omitted from the INSERT list, not when you explicitly insert NULL.

Provenance

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

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

Thread query: SQL "cannot insert null into column" debugging

TL;DR: Find the NULL-producing rows in your SELECT before the INSERT runs, then decide: COALESCE them to a default, filter them out, or fix the source. The error means a NOT NULL constraint rejected a NULL value, so the fix is either stop producing NULLs or stop forbidding them, and you want evidence for which is right.