Ratio or percentage metric divides directly by a column that can be zero: for example `sold / listed` where a product has listings but no sales yet — `listed` is nonzero but if the columns are swapped it becomes zero, or a new category with zero listed items is introduced upstream.. Missing NULLIF() guard on a denominator that was always non-zero in development but hit zero in production data: the query `SELECT revenue / sessions` works fine in dev where session counts are always positive, but fails once a date partition with zero sessions is loaded.. Data quality regression in an upstream pipeline stage: a dbt model or ADF mapping dataflow replaces NULL with 0 as a default fill — turning what was safely NULL into a hard division-by-zero trigger in the next transformation step.. Window function or aggregation produces an intermediate zero: for example `SUM(clicks) OVER (PARTITION BY campaign_id)` returns 0 for a campaign with no clicks in the time window, and a subsequent step divides impressions by that sum.. Schema or business logic change shifts which column is the denominator: a refactored metric definition changes `metric_a / metric_b` to `metric_b / metric_a` without updating the zero-guard, exposing a code path that was never tested.. Incremental load introduces a new cohort or segment that did not exist during model development: a new regional market, product line, or customer segment has zero values in the denominator column for the first few periods until data accumulates.. Modulo operation (`%`) on a zero divisor in a row-numbering or partitioning expression: `row_number % batch_size` fails when `batch_size` is read from a configuration table and the value was accidentally set to 0.

Step 1: Identify the exact failing expression. Run `EXPLAIN (ANALYZE, VERBOSE) ` or check the PostgreSQL error detail — it includes the position of the division expression. The error message reads: `ERROR: division by zero` with SQLSTATE 22012.. Step 2: Wrap the denominator with NULLIF to convert zero to NULL, which propagates silently instead of erroring: `SELECT numerator / NULLIF(denominator, 0) AS ratio FROM your_table;` — NULL in the output is far safer than a crashed pipeline.. Step 3: If NULL is not an acceptable result, add a CASE WHEN guard with an explicit fallback: `SELECT CASE WHEN denominator = 0 THEN 0 ELSE numerator / denominator END AS ratio FROM your_table;` — choose the fallback value that makes business sense (0, NULL, or a sentinel).. Step 4: Locate the upstream source of the zero. Query the raw table to confirm whether zero is a data-quality issue or a valid business state: `SELECT COUNT(*), MIN(denominator), MAX(denominator) FROM your_table WHERE denominator = 0;` — if it should not be zero, trace it back to the pipeline stage that produced it.. Step 5: Add a data quality assertion in dbt (if applicable): `{{ dbt_utils.expression_is_true('denominator <> 0') }}` as a `warn` or `error` severity test on the source or staging model, so future zero values are caught before they reach the transformation.. Step 6: If the error occurs inside a stored procedure or function, add a guard at the top of the relevant block: `IF denominator = 0 THEN RAISE EXCEPTION 'denominator is zero for id=%', row_id; END IF;` — this gives a descriptive error instead of a generic SQLSTATE 22012.. Step 7: After the fix is deployed, verify no rows silently returned NULL where a numeric result was expected: `SELECT COUNT(*) FROM your_output_table WHERE ratio IS NULL;` — if the count is unexpectedly high, the NULLIF guard is masking a broader data issue that needs to be addressed upstream.

Low severitydata format

PostgreSQL Error:
22012

What does this error mean?

PostgreSQL raises SQLSTATE 22012 when a division or modulo operation encounters a zero in the denominator. At the database level, the query is immediately aborted and an error is returned — no row is written. In a data pipeline, this typically surfaces when a calculated metric column (conversion rate, margin percentage, rows-per-batch) divides by a column that legitimately holds zero for certain business states, or unexpectedly received zero after an upstream transformation dropped or nullified values. The engineer sees the pipeline job fail mid-run, leaving downstream tables either stale or partially populated depending on transaction boundaries.

Common causes

1Ratio or percentage metric divides directly by a column that can be zero: for example `sold / listed` where a product has listings but no sales yet — `listed` is nonzero but if the columns are swapped it becomes zero, or a new category with zero listed items is introduced upstream.
2Missing NULLIF() guard on a denominator that was always non-zero in development but hit zero in production data: the query `SELECT revenue / sessions` works fine in dev where session counts are always positive, but fails once a date partition with zero sessions is loaded.
3Data quality regression in an upstream pipeline stage: a dbt model or ADF mapping dataflow replaces NULL with 0 as a default fill — turning what was safely NULL into a hard division-by-zero trigger in the next transformation step.
4Window function or aggregation produces an intermediate zero: for example `SUM(clicks) OVER (PARTITION BY campaign_id)` returns 0 for a campaign with no clicks in the time window, and a subsequent step divides impressions by that sum.
5Schema or business logic change shifts which column is the denominator: a refactored metric definition changes `metric_a / metric_b` to `metric_b / metric_a` without updating the zero-guard, exposing a code path that was never tested.
6Incremental load introduces a new cohort or segment that did not exist during model development: a new regional market, product line, or customer segment has zero values in the denominator column for the first few periods until data accumulates.
7Modulo operation (`%`) on a zero divisor in a row-numbering or partitioning expression: `row_number % batch_size` fails when `batch_size` is read from a configuration table and the value was accidentally set to 0.

How to fix it

1Step 1: Identify the exact failing expression. Run `EXPLAIN (ANALYZE, VERBOSE) <your query>` or check the PostgreSQL error detail — it includes the position of the division expression. The error message reads: `ERROR: division by zero` with SQLSTATE 22012.
2Step 2: Wrap the denominator with NULLIF to convert zero to NULL, which propagates silently instead of erroring: `SELECT numerator / NULLIF(denominator, 0) AS ratio FROM your_table;` — NULL in the output is far safer than a crashed pipeline.
3Step 3: If NULL is not an acceptable result, add a CASE WHEN guard with an explicit fallback: `SELECT CASE WHEN denominator = 0 THEN 0 ELSE numerator / denominator END AS ratio FROM your_table;` — choose the fallback value that makes business sense (0, NULL, or a sentinel).
4Step 4: Locate the upstream source of the zero. Query the raw table to confirm whether zero is a data-quality issue or a valid business state: `SELECT COUNT(*), MIN(denominator), MAX(denominator) FROM your_table WHERE denominator = 0;` — if it should not be zero, trace it back to the pipeline stage that produced it.
5Step 5: Add a data quality assertion in dbt (if applicable): `{{ dbt_utils.expression_is_true('denominator <> 0') }}` as a `warn` or `error` severity test on the source or staging model, so future zero values are caught before they reach the transformation.
6Step 6: If the error occurs inside a stored procedure or function, add a guard at the top of the relevant block: `IF denominator = 0 THEN RAISE EXCEPTION 'denominator is zero for id=%', row_id; END IF;` — this gives a descriptive error instead of a generic SQLSTATE 22012.
7Step 7: After the fix is deployed, verify no rows silently returned NULL where a numeric result was expected: `SELECT COUNT(*) FROM your_output_table WHERE ratio IS NULL;` — if the count is unexpectedly high, the NULLIF guard is masking a broader data issue that needs to be addressed upstream.

Example log output

ERROR:  division by zero
DETAIL:  An exception occurred in the function: compute_conversion_rate at line 14.
CONTEXT:  SQL function "compute_conversion_rate" during inlining

Frequently asked questions

How do I find which column is causing the 22012 division by zero error?

PostgreSQL's error message does not name the column directly, but `EXPLAIN (ANALYZE, VERBOSE)` shows the expression tree. Alternatively, run the query with `LIMIT 0` to confirm it parses, then narrow down with `WHERE denominator_column = 0 LIMIT 1` — once you find a row that matches, you have the offending data. In dbt, the compiled SQL in `target/compiled/` shows the exact expression.

Will NULLIF slow down my query significantly?

No. NULLIF is evaluated per row but adds negligible overhead — it is equivalent to a single conditional branch. For large tables the optimizer treats it as a cheap scalar expression. The bigger performance consideration is whether the NULL result then propagates into aggregations: SUM() and AVG() skip NULLs, so totals remain correct, but COUNT(*) versus COUNT(column) semantics differ.

Can I configure PostgreSQL to return NULL automatically instead of raising 22012?

No. PostgreSQL does not have a session-level setting to suppress division-by-zero errors the way some other databases do. The guard must be written into the query or function. If you control a custom function, you can catch the exception with a PL/pgSQL `EXCEPTION WHEN division_by_zero THEN RETURN NULL;` block, but that only works inside procedural code, not plain SQL expressions.

My pipeline retries on failure — will retrying fix a 22012 error?

No. The error is deterministic: as long as the zero value exists in the data and the query is unchanged, every retry will produce the same 22012. Retrying makes sense for transient errors (network, lock timeouts) but not for data or logic errors. Fix the query with NULLIF or CASE WHEN, or remove the zero value upstream, before retrying the pipeline run.

Source · www.postgresql.org/docs/current/errcodes-appendix.html