How to Fix PostgreSQL 'ERROR: division by zero' in CASE WHEN Expressions

Threat/Impact Level: HIGH | Downtime Risk: HIGH | Time to Fix: 5 mins

---

TL;DR

• What broke: A CASE WHEN branch executed a division expression where the denominator resolved to 0 at runtime, causing PostgreSQL to abort the entire query with a fatal error.
• How to fix it: Wrap every denominator in NULLIF(denominator, 0) so PostgreSQL returns NULL instead of throwing. Use COALESCE to substitute a safe fallback value.
• Fast path: Use our Client-Side Sandbox above to paste your failing query and auto-refactor every unguarded division in seconds.

---

The Incident (What Does the Error Mean?)

PostgreSQL throws this at query execution time — not parse time:

ERROR:  division by zero
CONTEXT:  SQL function "your_function" during inlining

The engine evaluated a CASE WHEN branch, reached a division expression, and the denominator was 0. PostgreSQL does not silently return NULL or infinity — it hard-aborts the transaction. Every row in the result set is lost. If this runs inside a larger transaction, the entire transaction is rolled back. In a reporting pipeline or API query, this surfaces as a 500 to the end user.

The deceptive part: PostgreSQL does NOT short-circuit CASE WHEN lazily in all contexts. Specifically, functions called within CASE arms can be evaluated eagerly by the planner. You cannot rely on branch ordering to protect you.

---

The Attack Vector / Blast Radius

This is a runtime data-driven failure, meaning it only appears when production data contains a zero in a denominator column. It passes all unit tests written against clean seed data.

Cascading failure chain:

1. A new customer record, a monthly reset, or a legitimate 0 value enters the denominator column.
2. The next scheduled report query or API call hits the row and aborts.
3. If the query is inside a BEGIN block or called from application ORM code, the entire transaction rolls back — including unrelated writes that preceded it.
4. Connection pool threads stall waiting for retries. Under load, this exhausts the pool.
5. Monitoring alerts fire on query error rate AND connection saturation simultaneously, making root cause diagnosis slower.

In aggregate functions: If this pattern appears inside a SUM(CASE WHEN ... THEN x/y END), a single bad row poisons the entire aggregation across millions of rows.

---

How to Fix It

Basic Fix — NULLIF Zero Guard

Wrap the denominator with NULLIF(col, 0). Division by NULL returns NULL in PostgreSQL, which is safe.

SELECT
  order_id,
  CASE
    WHEN status = 'complete' THEN
-     revenue / units_sold
+     revenue / NULLIF(units_sold, 0)
  ELSE
    0
  END AS revenue_per_unit
FROM orders;

Enterprise Best Practice — NULLIF + COALESCE with Explicit Fallback

Returning NULL is correct but can break downstream aggregations or BI tools that don't handle NULL gracefully. Chain COALESCE to enforce a business-defined fallback.

SELECT
  order_id,
  region,
  CASE
    WHEN status = 'complete' THEN
-     SUM(revenue) / SUM(units_sold)
+     COALESCE(SUM(revenue) / NULLIF(SUM(units_sold), 0), 0.00)
    WHEN status = 'refunded' THEN
-     refund_amount / original_quantity
+     COALESCE(refund_amount / NULLIF(original_quantity, 0), 0.00)
  ELSE
    0.00
  END AS adjusted_rate
FROM orders
GROUP BY order_id, region, status, refund_amount, original_quantity;

Why NULLIF over a CASE WHEN denominator = 0 THEN NULL ELSE denominator END inline guard? It is semantically identical but NULLIF is a single function call — the planner inlines it more cleanly and it is far less likely to be accidentally deleted during a code review.

For computed denominators (subqueries or CTEs):

WITH base AS (
  SELECT
    department_id,
    SUM(salary) AS total_salary,
    COUNT(*) AS headcount
  FROM employees
  GROUP BY department_id
)
SELECT
  department_id,
- total_salary / headcount AS avg_salary
+ COALESCE(total_salary / NULLIF(headcount, 0), 0) AS avg_salary
FROM base;

---

💡 Tired of pasting proprietary configs into ChatGPT? Generic AI tools log your company's ARNs, DB strings, and private keys. StackEngine is a zero-backend, pure Client-Side WASM utility. Drop your failing config into the sandbox above. We redact your secrets locally in the browser and auto-generate the refactored code using your own API key.

---

Prevention in CI/CD

The goal: make it impossible for an unguarded division inside a CASE WHEN to reach production.

1. Static Analysis with sqlfluff

Add a custom sqlfluff rule or use the L064 division-safety lint in strict mode. Gate your migration PR pipeline on it:

# .github/workflows/sql-lint.yml
- name: Lint SQL migrations
  run: |
    pip install sqlfluff
    sqlfluff lint migrations/ --dialect postgres --rules L064

2. pgTAP Unit Tests Against Zero-Value Fixtures

Every query that performs division must have a pgTAP test case where the denominator column is 0:

-- test/test_revenue_per_unit.sql
SELECT plan(1);
INSERT INTO orders (order_id, status, revenue, units_sold)
  VALUES (9999, 'complete', 500.00, 0);
SELECT lives_ok(
  $$ SELECT revenue / NULLIF(units_sold, 0) FROM orders WHERE order_id = 9999 $$,
  'Division by zero guard holds for units_sold = 0'
);
SELECT finish();

3. Database-Level CHECK Constraint (Defense in Depth)

If the denominator column should never be zero by business logic, enforce it at the schema level so the bad data never lands:

ALTER TABLE orders
  ADD CONSTRAINT chk_units_sold_positive
  CHECK (units_sold > 0);

This does not replace the NULLIF guard — it is a complementary layer. The guard protects you from legacy data and tables you don't own.

4. OPA / Conftest for Infrastructure-as-Code SQL Migrations

If you manage schema migrations via Terraform (e.g., terraform-provider-sql) or Flyway, add an OPA policy that rejects migration files containing bare / division without NULLIF:

# policy/no_bare_division.rego
package sql.safety

deny[msg] {
  input.sql_content
  regex.match(`/\s*[a-zA-Z_][a-zA-Z0-9_.]*(?!\s*NULLIF)`, input.sql_content)
  msg := "Bare division detected without NULLIF zero-guard. Wrap denominator in NULLIF(col, 0)."
}

Run via conftest test --policy policy/ migration.sql in your PR pipeline.