Incident brief
Division by zero
An expression divided a number by zero. PostgreSQL raises an error instead of returning infinity or an undefined result.
In 10 seconds
- What
- Division by zero
- What triggers it
- Run any expression that divides by a literal or computed zero.
- The fix
- Guard the divisor with NULLIF(divisor, 0) so a zero divisor produces NULL instead of an error.
- Proof
- Reproduced on PostgreSQL 16.14 → 10 / 0 was rejected with SQLSTATE 22012. Outside a transaction block, the session was unaffected — the very next statement ran and returned a normal result.
The fix
What to do right now
The immediate, application-level response to this error.
- Guard the divisor with NULLIF(divisor, 0) so a zero divisor produces NULL instead of an error.
- Validate the divisor in application code before sending the query, if a specific fallback value is required instead of NULL.
- Inside a transaction, remember this error aborts the transaction just like any other — see SQLSTATE 25P02.
-- NULLIF turns a zero divisor into NULL instead of raising an error
SELECT 10 / NULLIF(0, 0) AS guarded_result;Diagnose
See it live on the server
Run these against the affected instance to confirm the diagnosis before you act.
Standard triage — not specific to this error
These are canonical PostgreSQL system-catalog queries, shown as SQL to run. No sample output is attached because this is general triage, not a captured lab transcript.This SQLSTATE does not have an error-specific live snapshot yet. These are the canonical system-catalog queries you run against the affected server to see the problem in real time — standard triage, not a reproduced transcript.
What is running right now
Active backends, how long each has been running, and what it is waiting on.
SELECT pid,
state,
wait_event_type,
wait_event,
now() - query_start AS running_for,
left(query, 80) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
AND pid <> pg_backend_pid()
ORDER BY running_for DESC NULLS LAST;Who is blocking whom
Turn raw blocking PIDs into the actual queries on both sides of the wait.
SELECT blocked.pid AS blocked_pid,
blocked.query AS blocked_query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query
FROM pg_stat_activity AS blocked
JOIN LATERAL unnest(pg_blocking_pids(blocked.pid)) AS b(pid) ON true
JOIN pg_stat_activity AS blocking ON blocking.pid = b.pid
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;Locks that are still waiting
Every lock a backend has requested but not yet been granted.
SELECT l.pid,
l.locktype,
l.mode,
l.granted,
COALESCE(c.relname, l.transactionid::text) AS object
FROM pg_locks l
LEFT JOIN pg_class c ON c.oid = l.relation
WHERE NOT l.granted
ORDER BY l.pid;Why it happens
What PostgreSQL is telling you
The mechanism behind the error, grounded in the official manual — not paraphrased.
PostgreSQL 16 Documentation — §9.18 Conditional Expressions
A constant 1/0 subexpression will usually result in a division-by-zero failure at planning time, even if it's within a CASE arm that would never be entered at run time.Read the full section on postgresql.org →
Step 1: divide by zero
PostgreSQL cannot divide by zero, so it raises SQLSTATE 22012 immediately instead of returning a result.Step 2: an unrelated statement, right after
Because this wasn't inside an explicit BEGIN/COMMIT block, the failed statement didn't poison anything — the next unrelated query ran normally and returned 5.Reproduce & verify
A real, single-session PostgreSQL reproduction
A literal transcript of SQL run against a live PostgreSQL instance in an isolated lab — the commands below are exactly what was executed.
- 1Run any expression that divides by a literal or computed zero.
- 2PostgreSQL rejects the statement with SQLSTATE 22012 instead of returning a result.
- 3Outside of an explicit transaction, the session itself is unaffected — the next statement runs normally.
One client, no schema involved: divide by zero, then run an unrelated statement to confirm the session still works.
-- No schema needed: this error only involves scalar arithmetic on literals.
SELECT 1;SELECT 10 / 0;-- outside an explicit transaction, the session is not broken by the error above
SELECT 10 / 2;What PostgreSQL actually returned
?column?
----------
1
(1 row)ERROR: division by zero ?column?
----------
5
(1 row)NULLIF(divisor, 0) was tested directly against both a zero and a non-zero divisor, proving it turns the error into a plain NULL result without changing behavior for normal values.
Run the same guarded expression once with a zero divisor and once with a real divisor.
Without this
Above: an unguarded division by zero raises an error.
With this, tested
Below: the same division, guarded with NULLIF, returns NULL for zero and the normal result otherwise.
What Pro unlocks here
- The exact prevention SQL — copy-paste ready
- Raw psql output captured from the Docker lab
- A senior-DBA action list to take it further
- Live monitoring queries to catch it in production
- The deeper audit: fix-that-fails counterexample, GUC before/after, server-log evidence
Related & next steps
Follow the thread
Everything this error touches — jump straight to the sibling error, term, runbook, or parameter.
Verification
- Last verified
- 2026-07-15 (Docker lab, PostgreSQL 16.14)
- Reviewed by
- Verified against PostgreSQL 16.14 in an isolated lab environment
- Audit status
- reviewed