🚨 Postgres Error: 'division by zero' — Cause, Fix, and Troubleshooting Guide

Overview

PostgreSQL raises this error the moment an arithmetic expression tries to divide (or take a modulo) by zero. It aborts the whole statement — one bad row poisons the entire query — because the result is mathematically undefined.

ERROR:  division by zero

It fires for both the division operator / and the modulo operator %, and it does not matter whether the zero is a literal, a column value, or the result of a subexpression that happens to evaluate to 0. Integer and numeric division both raise it; only floating-point division by zero produces Infinity/NaN instead.

Symptoms

• A SELECT, UPDATE, or view query fails with ERROR: division by zero for some inputs but not others.
• A ratio or percentage report works on most days and breaks the day a group has no rows or a zero total.
• The failure is data-dependent: the same SQL succeeds against one dataset and fails against another.

SELECT id, revenue / units AS revenue_per_unit
FROM orders;

ERROR:  division by zero

One orders row with units = 0 aborts the entire result set, not just that row.

Common Root Causes

1. The denominator is literally 0 or evaluates to 0

A column such as units, quantity, or weight holds 0 for some rows, and the expression divides by it directly.

SELECT id, total_cents / quantity AS unit_cost
FROM orders
WHERE quantity = 0;

Any row returned here will blow up the division.

2. Ratio or percentage over an empty or zero-sum group

Aggregate math like count(*) FILTER (...) / count(*) fails when the group’s denominator aggregates to 0.

SELECT customer_id,
       count(*) FILTER (WHERE status = 'shipped') / count(*) FILTER (WHERE status = 'paid') AS ship_rate
FROM orders
GROUP BY customer_id;

Customers with zero paid orders make the denominator 0.

3. Modulo by zero

The % operator raises the same error, which surprises people using it for bucketing or round-robin sharding.

SELECT id % shard_count AS shard
FROM users;

Any row with shard_count = 0 fails.

4. Integer division hiding a zero

Integer division truncates, so a small numerator over a larger denominator yields 0 — but the denominator itself being 0 still errors. Mixing integer columns also means you never get a fractional result that would have signalled the problem earlier.

How to diagnose

Step 1: Find the rows where the divisor is zero

Point directly at the offending column before touching the division.

SELECT id, quantity
FROM orders
WHERE quantity = 0
LIMIT 20;

   id   | quantity
--------+----------
 100482 |        0
 100517 |        0
(2 rows)

Step 2: Reproduce the exact expression on those rows

Inspect the full expression, not just the column, in case the denominator is computed.

SELECT id, quantity, (quantity - returned_qty) AS net_qty
FROM orders
WHERE quantity - returned_qty = 0
LIMIT 20;

A non-zero column can still produce a zero denominator once you subtract another column.

Step 3: Confirm which operator is failing

If the query has several / and % operators, isolate them one at a time so you fix the right expression rather than guessing.

SELECT id,
       revenue,
       units,
       revenue / NULLIF(units, 0) AS rev_per_unit
FROM orders
LIMIT 5;

If wrapping one operator in NULLIF stops the error, that operator was the culprit.

Fixes

Guard the denominator with NULLIF

NULLIF(divisor, 0) returns NULL when the divisor is 0, and dividing by NULL yields NULL instead of erroring. This is the idiomatic Postgres fix.

SELECT id, revenue / NULLIF(units, 0) AS revenue_per_unit
FROM orders;

The same pattern works for modulo:

SELECT id, value % NULLIF(shard_count, 0) AS shard
FROM users;

Use CASE for explicit control

When you want a value other than NULL, branch on the denominator directly.

SELECT id,
       CASE WHEN units = 0 THEN NULL
            ELSE revenue / units
       END AS revenue_per_unit
FROM orders;

Provide a default with COALESCE

Wrap the NULLIF result in COALESCE to substitute a sensible fallback such as 0.

SELECT id, COALESCE(revenue / NULLIF(units, 0), 0) AS revenue_per_unit
FROM orders;

Filter the zero rows out in WHERE

If zero-divisor rows are meaningless for the report, exclude them before the division ever runs.

SELECT id, revenue / units AS revenue_per_unit
FROM orders
WHERE units <> 0;

What to watch out for

• NULLIF turns the result into NULL, which then propagates through downstream SUM/AVG — decide whether NULL or a default like 0 is correct for your report.
• The denominator can be a whole subexpression (a - b, x * y); guard the full expression, not just a single column.
• Modulo % raises the identical error as /; apply the same NULLIF guard to it.
• Casting to float8/double precision avoids the error (it returns Infinity/NaN) but usually hides a real data problem rather than fixing it.
• A CHECK (units <> 0) constraint or a NOT NULL default stops zero denominators entering the table in the first place.

Related

• Postgres Error: ‘invalid input syntax for type integer’
• Postgres Error: ‘integer out of range’
• Postgres Error: ‘value too long for type character varying’