PostgreSQL: division by zero

What it means

A / or % had zero on the right-hand side. PostgreSQL raises an error for every numeric type: integer, bigint, numeric, and even double precision, where some other systems return infinity. The whole statement fails and nothing it did is kept. Inside a transaction block, the transaction is aborted until you roll back.

The message doesn’t say which row or which expression. In a query over many rows, it means at least one row had a zero divisor, most often a count, a total or a quantity that was zero for that row.

Common causes

A ratio over data that can be zero: average order value with zero orders, a percentage of a total that’s zero, a conversion rate on a day with no visits.
An aggregate that comes out as zero: count(*) FILTER (…) or sum(…) over no matching rows used as a divisor.
Relying on WHERE or AND to skip the bad rows. PostgreSQL doesn’t promise to evaluate conditions in the order you write them.
A constant zero after a parameter or a template filled in 0. PostgreSQL works out constant expressions while planning, so 1/0 fails even when no row would ever reach it.

How to fix it

Turn a zero divisor into NULL

NULLIF(x, 0) returns NULL when x is zero, and dividing by NULL gives NULL instead of an error:

SELECT day, revenue / NULLIF(orders, 0) AS avg_order
FROM order_stats;

The row with no orders gets an empty avg_order. If you want a number there, add a default: coalesce(revenue / NULLIF(orders, 0), 0). Only do that when zero is a true answer; for an average it usually isn’t, and NULL (“no value”) is more honest.

The same works for percentages:

SELECT round(100.0 * sum(orders) FILTER (WHERE revenue > 1000) / NULLIF(sum(orders), 0), 1) AS pct
FROM order_stats;

Say what zero means with CASE

CASE evaluates only the branch it picks, so it is the documented way to guard an expression:

SELECT day,
       CASE WHEN orders > 0 THEN revenue / orders END AS avg_order
FROM order_stats;

Two exceptions, both in the PostgreSQL documentation and both reproduced below: CASE can’t stop an aggregate inside it from being computed (aggregates run before the CASE around them), and it can’t stop a constant like 1/0 being worked out while the query is planned.

Don’t rely on WHERE or AND to guard it

SELECT day FROM order_stats WHERE orders > 0 AND revenue / orders > 50;

This happened to work on our test server, but the planner may evaluate the two conditions in either order, or push one into an index scan. Use NULLIF or CASE inside the expression itself.

Fix aggregates at the source

For avg(revenue / orders), divide inside with NULLIF: avg(revenue / NULLIF(orders, 0)). avg skips NULLs, so days without orders drop out of the average instead of breaking it.

Reproduce it

On PostgreSQL 18.6, with three days of stats, one of them with no orders:

CREATE TABLE order_stats (day date PRIMARY KEY, revenue numeric(12,2), orders int);
INSERT INTO order_stats VALUES
  ('2026-10-01', 1250.00, 25), ('2026-10-02', 0, 0), ('2026-10-03', 980.50, 14);

SELECT day, revenue / orders AS avg_order FROM order_stats ORDER BY day;

ERROR:  division by zero

SELECT 1/0, SELECT 1.0/0, SELECT 7 % 0 and SELECT 1.0::float8 / 0 all give the same error. With NULLIF:

    day     |      avg_order
------------+---------------------
 2026-10-01 | 50.0000000000000000
 2026-10-02 |
 2026-10-03 | 70.0357142857142857
(3 rows)

The CASE exceptions, from the same session:

-- No row matches, but 1/0 is a constant, worked out while planning:
SELECT day, CASE WHEN orders > 0 THEN 1/0 END FROM order_stats WHERE orders < 0;
-- avg() runs before the CASE around it:
SELECT CASE WHEN min(orders) > 0 THEN avg(revenue / orders) END FROM order_stats;

Both fail with division by zero. With \set VERBOSITY verbose, psql shows the code: ERROR: 22012: division by zero. PostgreSQL 14.24 gives the same messages.

In Inlet

When a statement fails, Inlet shows the server’s error. With your own Anthropic API key, Ask Claude (Pro, ⌘L) can fix the failed statement; it sends the schema, the SQL and the error, never rows.

division by zero