22012 — division_by_zero

At a glance

A division or modulo operation reached a zero divisor. PostgreSQL 18.6 uses SQLSTATE 22012 in numeric, integer, floating-point, money, and interval-division paths, while each path keeps its own operand and special-value rules.

SELECT 10::numeric / 0::numeric;
SELECT 10::numeric / 2::numeric;

The retained PostgreSQL 18.6 and 10.21 observations show the first statement returning primary division by zero with SQLSTATE 22012, then the corrected statement returning 5.0000000000000000; both backends were IDLE after the repaired action. The public case JSON identifies this numeric division case; it is not a runtime comparison of every producer listed below.

Meaning

The direct numeric functions numeric_div and numeric_mod guard a zero numeric divisor; their internal *_opt_error forms can instead set a have_error flag and return NULL to a caller that handles the failure. Integer division and modulo functions check their zero second operand before using / or %. Float4/float8 division routes through float*_div, which raises for a zero divisor when the numerator is not NaN. Money division checks its integer or money divisor, and interval division checks its float factor. These paths share the SQLSTATE and primary text, but the resolved operator and special values still determine the result.

Diagnosis

Locate the denominator or modulo divisor after operator/type resolution; do not infer an int4 rule for all numeric types. Check whether zero came from input, a join, an aggregate, or a business rule, and inspect the complete primary message. A NULL operand normally produces a NULL result before a strict arithmetic function is called, while NULLIF(denominator, 0) deliberately turns the zero case into NULL. A CASE expression can choose a NULL, substitute, or skip policy, so choose the branch that matches the business result rather than silently changing an error into a value.

The planning boundary matters: the PostgreSQL 18.6 conditional-expression documentation warns that a constant 1/0 subexpression can fail during planning even in an unreachable CASE arm. The planner source recursively simplifies constant arguments and evaluates immutable constant expressions. Use a non-constant value or guard the actual denominator with NULLIF/a suitable CASE when the division must be deferred to execution; this does not make an invalid constant expression safe, and it does not decide whether NULL or a substitute is correct for the application.

Response

Repair the producer of the zero divisor or select an explicit zero policy. Use NULLIF when a NULL result is meaningful, or a CASE branch when the application has a documented substitute or skip rule; then validate the downstream aggregates and filters that will see that result. Do not replay the unchanged operation. The ordinary fixed paths raise ERROR; in an explicit transaction use ROLLBACK or ROLLBACK TO SAVEPOINT before retrying, while autocommit can retry only the corrected action. The retained 18.6/10.21 numeric observation is limited to this case.

Messages

• Primary, ERROR: division by zero.
• No fixed DETAIL or HINT is attached to the cited numeric, integer, float, money, or interval-division guards. Internal numeric have_error callers are a soft handling path, not a claim that a direct SQL operator returns successfully.

Versions

The locked catalogue records this condition from 7.4; fixed source coverage is PostgreSQL 18.6. The retained numeric runtime records are PostgreSQL 18.6 and 10.21 and cover only 10::numeric / 0::numeric followed by 10::numeric / 2::numeric.

Related

22003, 22008, 22015

Sources

• src/backend/utils/adt/numeric.c#L3243-L3375 covers numeric division, special values, the direct ERROR, and the internal have_error branch; #L3467-L3553 covers numeric modulo.
• src/backend/utils/adt/int.c#L861-L875 and #L1158-L1184 show int division and modulo; int8.c has the corresponding bigint guards.
• src/include/utils/float.h#L222-L250 and src/backend/utils/adt/float.c#L101-L106 show float4/float8 zero-divisor dispatch and the primary.
• src/backend/utils/adt/cash.c#L155-L161 and #L728-L744 show money division guards; src/backend/utils/adt/timestamp.c#L3759-L3776 shows interval division by a zero factor.
• src/backend/optimizer/util/clauses.c#L2437-L2462 shows constant simplification/evaluation; the same-tag conditional-expression documentation states the unreachable-CASE 1/0 planning boundary.

The structured evidence record retains both runtime summaries/raw digests and the fixed source claims.