PostgreSQL Floating Point Exception (SQLSTATE 22P01)

PostgreSQL raises SQLSTATE 22P01 (floating_point_exception) when a floating-point arithmetic operation produces an exceptional result — such as division by zero, overflow to infinity, or an invalid operation that would yield NaN (Not a Number). You will see this in psql or application logs as: ERROR: floating-point exception.

What This Error Means

SQLSTATE 22P01 belongs to class 22 — "Data Exception" — which covers errors arising from invalid data values during SQL operations. The specific condition floating_point_exception is triggered by the underlying hardware or IEEE 754 floating-point unit signaling a fault that PostgreSQL cannot represent as a valid finite result.

PostgreSQL's float4 (real) and float8 (double precision) types follow the IEEE 754 standard. Under normal circumstances, IEEE 754 permits infinity and NaN as values, meaning many operations that would be exceptions in other contexts silently produce Infinity or NaN in PostgreSQL. The 22P01 error is raised only when the CPU floating-point unit signals a hardware exception that PostgreSQL explicitly traps — most commonly on platforms or builds where the floating_point_exception signal (SIGFPE) is delivered.

When this error occurs, the current statement is aborted and the transaction is placed in an aborted state. Any subsequent statements in the same transaction block will fail with ERROR: current transaction is aborted, commands ignored until end of transaction block until a ROLLBACK (or ROLLBACK TO SAVEPOINT) is issued.

Common Causes

1. Division by zero on a floating-point column. Dividing a float4 or float8 value by a zero literal or a zero-valued expression. On some PostgreSQL builds this produces Infinity silently; on others, particularly older versions or certain OS/compiler combinations, it raises 22P01.

2. Arithmetic overflow producing infinity. Multiplying or exponentiating very large float8 values to exceed the representable range (~1.8 × 10^308), causing overflow to Inf.

3. Invalid operations producing NaN. Operations like sqrt(-1.0) or 0.0 / 0.0 on floating-point types that the platform traps rather than returning IEEE 754 NaN silently.

4. Custom C functions or extensions. User-defined C functions that perform raw floating-point arithmetic without enabling IEEE 754 non-stop mode may cause the PostgreSQL backend to receive a SIGFPE, which is translated into this error.

5. Platform-specific behavior. The error is more common on certain operating system and processor combinations. Linux on x86-64 with PostgreSQL built with specific compiler flags may trap conditions that other platforms allow silently.

How to Fix floating_point_exception

1. Guard against division by zero using NULLIF.

   -- Instead of:
   SELECT numerator / denominator FROM measurements;
   
   -- Use NULLIF to return NULL instead of dividing by zero:
   SELECT numerator / NULLIF(denominator, 0.0) FROM measurements;
   

2. Check for problematic values with a CASE expression.

   SELECT
     CASE
       WHEN denominator = 0.0 THEN NULL
       ELSE numerator / denominator
     END AS safe_ratio
   FROM measurements;
   

3. Validate inputs before performing floating-point math. Filter out rows where the divisor or operand would produce an exception:

   SELECT numerator / denominator
   FROM measurements
   WHERE denominator <> 0.0
     AND denominator IS NOT NULL;
   

4. Use numeric instead of float8 for precision-sensitive calculations. The arbitrary-precision numeric type raises a clear division_by_zero error (SQLSTATE 22012) instead of an ambiguous floating-point exception, and never produces infinity or NaN silently:

   -- Cast to numeric for controlled behavior:
   SELECT (numerator::numeric) / NULLIF(denominator::numeric, 0) FROM measurements;
   

5. Wrap risky operations in a savepoint so a single bad row does not abort the entire transaction:

   BEGIN;
   SAVEPOINT before_calc;
   SELECT risky_float_expression FROM large_table;
   -- If it fails:
   ROLLBACK TO SAVEPOINT before_calc;
   COMMIT;
   

6. Inspect C extensions or user-defined functions that operate on float values. Ensure they set the FPU to non-stop (IEEE 754 non-trap) mode, or validate inputs before performing operations that could generate hardware exceptions.

Additional Information

• SQLSTATE class 22 siblings relevant to numeric errors include 22012 (division_by_zero), 22003 (numeric_value_out_of_range), and 22023 (invalid_parameter_value). Division by zero on integer or numeric types raises 22012, not 22P01.
• The P in 22P01 indicates a PostgreSQL-specific extension to the SQL standard — it is not part of the SQL:2011 or later standards' defined SQLSTATE codes.
• Most PostgreSQL builds on Linux x86-64 follow IEEE 754 permissively for float8 — meaning 1.0/0.0 returns Infinity and 0.0/0.0 returns NaN without raising 22P01. The error is more likely to surface in PostgreSQL builds compiled with -mfpmath=387 or on certain BSD/macOS platforms.
• JDBC, psycopg2, and most other drivers surface 22P01 as a generic SQLException or DatabaseError with the SQLSTATE code in the error object. Check e.pgcode (psycopg2) or getSQLState() (JDBC) to distinguish it programmatically.
• If you encounter this error in production and cannot reproduce it locally, the discrepancy may be due to differing OS or compiler environments between your development machine and your server.

Frequently Asked Questions

Why does 1.0 / 0.0 return Infinity in some PostgreSQL installations but raise an error in others?

PostgreSQL's floating-point behavior depends on the host operating system's FPU mode and the C compiler flags used when PostgreSQL was built. Most modern Linux x86-64 builds default to IEEE 754 non-stop mode, where 1.0/0.0::float8 silently returns Infinity. On platforms where the FPU traps the exception, PostgreSQL catches the resulting SIGFPE and converts it to error 22P01. The behavior is consistent within a given installation, so test with your production build.

Does this error leave my connection in a broken state?

Yes, if the error occurs inside an explicit transaction block (BEGIN ... COMMIT), PostgreSQL marks the transaction as aborted. You must issue ROLLBACK before the connection can be used for new work. If the error occurs outside a transaction block (a single statement), no rollback is needed — the statement simply fails and the connection remains usable.

Should I use float8 or numeric to avoid this error?

For financial or precision-critical calculations, prefer numeric. It never produces infinity or NaN, raises deterministic errors (22012 for division by zero), and is consistent across platforms. float8 is appropriate for scientific data, aggregates over large datasets, and cases where approximate results and IEEE 754 semantics are acceptable — but you should always guard divisors with NULLIF or CASE.

Can this error come from a query I didn't write, such as an ORM-generated query?

Yes. ORMs that map application-level division or aggregation to SQL float arithmetic can trigger this. Check your ORM's generated SQL (enable query logging) and add input validation or use NULLIF at the database layer to handle zero denominators before the ORM query reaches the server.