↑↓ select ↵ open ⌫ change scope Open full search

PG.CENTER connects PostgreSQL documentation, reference, and ecosystem knowledge. Maintained by Pigsty.

SQLSTATE / CLASS 22 · DATA EXCEPTION

22012

Read PG 18 manual ↗

division_by_zero

SQLSTATE
22012
Condition name
division_by_zero
Class
Data Exception
Source macro
ERRCODE_DIVISION_BY_ZERO
Evidence
Observed at runtime in the source evidence
Analysis & operational context

English SQLSTATE atlas: authored explanations and source/runtime evidence are separate from the manual definitions. View source ↗

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.

Sources

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

Source evidence

Evidence belongs to the frozen source and runtime versions listed here. It is not a runtime verification of the selected manual version.

22012 is division_by_zero in SQLSTATE Class 22.

Method: Read the fixed errcodes row and macro.

src.errcodes.18.6

Numeric division and modulo guard zero divisors; direct SQL calls raise, while internal opt-error forms can report through a have_error soft path.

Method: Read the complete numeric division and modulo functions.

src.numeric-div.18.6 src.numeric-mod.18.6

The int4 and int8 division and modulo functions check their second operand before using the arithmetic operator.

Method: Read the representative integer and bigint guards.

src.int-div.18.6 src.int-mod.18.6 src.int8-div.18.6 src.int8-mod.18.6

float4/float8 division raises for a zero divisor only when the numerator is not NaN, through the inline zero-divide helper.

Method: Read the exact val2==0 and !isnan(val1) guard.

src.float-inline.18.6 src.float-error.18.6

Money division checks its divisor and interval division rejects a zero factor with the same condition.

Method: Read the type-specific division guards.

src.cash-int.18.6 src.cash-money.18.6 src.interval-div.18.6

The planner can evaluate an immutable constant division while simplifying expressions; same-tag conditional-expression documentation calls out unreachable CASE 1/0 as a planning-time failure boundary.

Method: Read the planner evaluation path and same-tag documentation.

Limits: This is a planning/execution boundary, not an assertion that every CASE expression is evaluated early.

src.planner-const.18.6 src.docs-conditional.18.6

The retained numeric division case observed 22012 for 10::numeric / 0::numeric and 5.0000000000000000 after 10::numeric / 2::numeric on PostgreSQL 18.6 and 10.21; both repaired sessions were IDLE.

Method: Read both retained summaries, raw digests, and the fixed snippet registry.

Limits: The observation covers this numeric case and does not generalize to every producer.

runtime.22012-data-runtime-latest.latest runtime.22012-data-runtime-pg10.pg10 snippet-registry.22012

The locked catalogue records 22012 from 7.4 without proving an exact implementation introduction date.

Method: Use catalogue presence and history boundaries.

src.errcodes.18.6

Message templates

ERROR · message.division-by-zero

Primary

division by zero

Direct cited guards; special values and internal numeric soft callers are type/context dependent.

Reproduction & repair cases

division_by_zero_numeric · PG 10, 18

Preconditions

  • A runner-owned disposable target is provisioned.

Trigger: Evaluate a numeric expression with a zero denominator.

Expected assertions

  • SQLSTATE is 22012 with primary division by zero
  • The failed autocommit session remains IDLE
  • A nonzero denominator returns the defined numeric result

Repair: Validate the denominator or apply an explicitly chosen domain policy; do not silently turn a calculation into NULL with NULLIF unless that is the intended business result.

Cleanup: Close the runner connection and drop the case schema with an owner connection.

Recorded runtime evidence

18.6 (Homebrew) · passed

Run: 22012-data-runtime-latest

{
  "sqlstate": "22012",
  "status_after_repair": "IDLE"
}
10.21 (Debian 10.21-1.pgdg90+1) · passed

Run: 22012-data-runtime-pg10

{
  "sqlstate": "22012",
  "status_after_repair": "IDLE"
}

Definition snapshot: english-manuals:91bbab6760ed934e46e985b4e0a… · English manual source