Fix PostgreSQL ERROR: division by zero in SQL Queries

What's Happening

You ran a query with division and PostgreSQL threw:

ERROR:  division by zero

The denominator evaluated to zero at runtime. PostgreSQL stops immediately — no partial results, no warnings, just a hard error.

The usual suspects:

• Percentage or ratio columns (sales / total_sales * 100)
• Manual averages (SUM(x) / COUNT(y)) when a group is empty
• Aggregation queries where some groups have zero records
• Revenue-per-user or similar KPI queries over sparse data

Reproducing the Error

Simplest case:

SELECT 10 / 0;
-- ERROR:  division by zero

In a real table:

SELECT
  department,
  total_revenue / headcount AS revenue_per_person
FROM department_stats;
-- Fine until a department has headcount = 0

The query only blows up when it actually hits a row with headcount = 0. Test data rarely has zeros — which is exactly why this shows up in production first, not dev.

Debug: Find the Offending Rows

Before touching the query, figure out what you're dealing with:

-- Which rows have a zero denominator?
SELECT department, headcount
FROM department_stats
WHERE headcount = 0;

-- For aggregation: spot empty groups in the source table
SELECT department, COUNT(*) AS row_count
FROM sales
GROUP BY department;

Is it 2 rows or 2,000? That changes which fix makes more sense.

Fix 1: NULLIF — The Quickest Fix

NULLIF(a, b) returns NULL when a = b, otherwise returns a. Wrap your denominator:

SELECT
  department,
  total_revenue / NULLIF(headcount, 0) AS revenue_per_person
FROM department_stats;

When headcount is 0, the division becomes total_revenue / NULL, which produces NULL — not an error. The row still appears in results, just with a NULL for that column.

Prefer a 0 over NULL? Add COALESCE:

SELECT
  department,
  COALESCE(total_revenue / NULLIF(headcount, 0), 0) AS revenue_per_person
FROM department_stats;

Fix 2: CASE WHEN — More Explicit Control

When the fallback isn't simple, CASE WHEN gives you full control:

SELECT
  department,
  CASE
    WHEN headcount = 0 THEN NULL
    ELSE total_revenue / headcount
  END AS revenue_per_person
FROM department_stats;

Or return a sentinel value your application can detect:

SELECT
  department,
  CASE
    WHEN headcount = 0 THEN -1  -- signals "no data" to the caller
    ELSE total_revenue / headcount
  END AS revenue_per_person
FROM department_stats;

CASE WHEN short-circuits — PostgreSQL checks the condition first and never attempts the division if the guard matches. Slightly more readable than nested NULLIF/COALESCE when business logic is involved.

Fix 3: Aggregation Queries

Division by zero in aggregation usually comes from empty groups:

-- COUNT = 0 for a category with no rows → boom
SELECT
  category,
  SUM(amount) / COUNT(order_id) AS avg_order_value
FROM orders
GROUP BY category;

Wrap COUNT with NULLIF:

SELECT
  category,
  SUM(amount) / NULLIF(COUNT(order_id), 0) AS avg_order_value
FROM orders
GROUP BY category;

Better yet — for averages, just use AVG(). It handles empty sets by returning NULL automatically, no extra wrapping needed:

-- AVG() never throws division by zero
SELECT category, AVG(amount) AS avg_order_value
FROM orders
GROUP BY category;

Fix 4: Percentage Calculations

Percentage queries are where this error appears most often. A typical pattern:

-- Risky: integer division + possible zero total
SELECT
  status,
  COUNT(*) * 100 / total AS pct
FROM orders
CROSS JOIN (SELECT COUNT(*) AS total FROM orders) t
GROUP BY status, total;

Safe version:

SELECT
  status,
  ROUND(
    COUNT(*) * 100.0 / NULLIF(total, 0),
    2
  ) AS pct
FROM orders
CROSS JOIN (SELECT COUNT(*) AS total FROM orders) t
GROUP BY status, total;

Two fixes in one: NULLIF guards against zero, and 100.0 (float) instead of 100 (integer) prevents truncation on the division. A query returning 33 instead of 33.33 is its own kind of bug.

Fix 5: Constraint at the Table Level

If a column like headcount should never be zero, say so in the schema:

ALTER TABLE department_stats
  ADD CONSTRAINT headcount_positive CHECK (headcount > 0);

Bad data never enters, so queries never hit the zero case. Pair it with NOT NULL:

ALTER TABLE department_stats
  ALTER COLUMN headcount SET NOT NULL,
  ADD CONSTRAINT headcount_positive CHECK (headcount > 0);

This is the most permanent fix — but only applies when zeros are genuinely invalid data, not legitimate empty states.

Verify the Fix

Quick sanity check with literals:

-- Should return NULL, not an error
SELECT 100 / NULLIF(0, 0);
-- Result: NULL

-- With COALESCE fallback
SELECT COALESCE(100 / NULLIF(0, 0), 0);
-- Result: 0

Test against your actual table using a CTE to inject a zero row:

WITH test_data AS (
  SELECT 'Engineering' AS dept, 0 AS headcount, 50000 AS revenue
)
SELECT
  dept,
  revenue / NULLIF(headcount, 0) AS rev_per_person
FROM test_data;
-- Expected: Engineering | NULL  (no error)

Then run the original failing query. It should complete cleanly now.

Quick Reference

• Simplest fix: / NULLIF(denominator, 0)
• Return 0 instead of NULL: COALESCE(x / NULLIF(y, 0), 0)
• Complex logic: CASE WHEN y = 0 THEN ... ELSE x / y END
• Averages: use AVG() — handles empty sets natively
• Prevent at source: add CHECK (column > 0) constraint

Lessons Learned

Nine times out of ten, the root cause is data quality. A column that was supposed to hold positive values silently accepted zeros because no constraint stopped it. NULLIF fixes the symptom in the query — but it's worth tracing back whether those zeros are legitimate empty states or a data pipeline bug.

For reporting and dashboards where sparse data is normal, NULLIF is the right default. For transactional queries where a zero denominator signals corrupted data, enforce a CHECK constraint at the table level and fix the upstream source.