PostgreSQL raises 22P01 floating_point_exception when a floating-point calculation overflows, underflows, or divides by zero.
floating_point_exception (PostgreSQL error 22P01) signals an invalid floating-point operation, usually divide-by-zero or numeric overflow. Review calculations, add zero-checks, or cast to NUMERIC to resolve the error.
PostgreSQL Error 22P01
Error 22P01 appears when PostgreSQL encounters an illegal floating-point operation such as divide-by-zero, overflow, underflow, or invalid cast to floating-point.
The backend halts the current statement to protect data integrity, so the query fails until the arithmetic problem is removed.
Division by zero in DOUBLE PRECISION or REAL columns is the most frequent cause. PostgreSQL detects the zero divisor and throws 22P01 before completing the calculation.
Overflow or underflow can occur when intermediate results exceed IEEE-754 limits, for example, exponentiation of very large numbers.
Invalid input during implicit casting to float, such as 'NaN' in strict modes, can also trigger the exception.
Add explicit zero checks in WHERE or CASE expressions to avoid divide-by-zero. Use NULLIF or FILTER to skip zero divisors.
Cast to NUMERIC with sufficient precision when working with large magnitudes to avoid overflow. Monitor results with PostgreSQL’s pg_stat_statements.
If the error happens inside PL/pgSQL, wrap the risky expression in BEGIN ... EXCEPTION blocks and handle it gracefully.
Analytics queries that compute rates suffer when denominator counts are zero. Protect them with NULLIF or COALESCE.
Import jobs that parse CSV strings into FLOAT columns can raise 22P01 when encountering malformed numbers. Validate input or load into TEXT first.
Financial models using exponential growth may overflow. Switch to NUMERIC or scale values down before exponentiation.
Always check denominators for zero. Use CHECK constraints to forbid zero in critical columns.
Prefer NUMERIC for money and high-precision data. Reserve floating-point types for scientific workloads where small rounding errors are acceptable.
Add automated tests that run boundary inputs through functions to catch overflow before production.
22012 division_by_zero – occurs for integer or NUMERIC division by zero. Fix by adding zero checks.
22003 numeric_value_out_of_range – raised when NUMERIC overflows its scale; increase precision or scale.
42804 datatype_mismatch – appears when implicit cast fails; use explicit CAST to resolve.
No rows are written or updated when the error occurs, so existing data remains safe.
Different hardware or PostgreSQL versions may handle IEEE overflows differently, showing the error only under certain configurations.
PostgreSQL does not allow disabling; protecting arithmetic integrity is mandatory. Handle or prevent the condition instead.
Galaxy’s inline query linting flags potential zero divisions and suggests NULLIF or FILTER clauses before you run the query.
.avif)
.avif){kind=logo}
