PostgreSQL: division by zero

ERRORSQLSTATE 22012division_by_zero

ERROR: division by zero

An expression divided, or took a modulo, by zero. Guard the divisor with NULLIF so a zero becomes NULL instead of an error, or handle the zero case explicitly.

What this error means

Postgres raises an error rather than returning infinity when you divide by zero. The divisor evaluated to 0 somewhere in the query, often a column, a count, or a computed ratio over an empty subset.

How to fix it

The divisor can be zero

Turn a zero divisor into NULL with NULLIF; the whole expression then yields NULL.

sql

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

You want a specific value when the divisor is zero

Use CASE to handle the zero case explicitly.

sql

SELECT CASE WHEN orders = 0 THEN 0 ELSE revenue / orders END FROM t;

How to avoid it next time

Guard every division whose denominator can be zero.
Prefer NULLIF for ratios so empty groups produce NULL, not an error.

Frequently asked questions

Why not return infinity or null automatically?

Postgres treats division by zero as an error to surface bad data. Use NULLIF(divisor, 0) to opt into a NULL result.

Does this apply to modulo too?

Yes. x % 0 raises the same error. Guard the divisor the same way.

Stop struggling with SQL

Use FluentDB, the AI database client for macOS.

Download for Mac PostgreSQL client for Mac

Part of the PostgreSQL error reference. Last reviewed 2026-07-31.