PostgreSQL 22P01 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22P01: Floating Point Exception

PostgreSQL error code 22P01 is raised when a floating-point operation produces an exceptional result that cannot be represented as a valid number. This typically occurs during division by zero on float types, operations involving NaN (Not a Number), or arithmetic that yields Infinity. It is most commonly encountered in analytics, financial calculations, and data pipelines processing external or sensor data.

Top 3 Causes

1. Division by Zero on Float Types

Unlike integer division (which raises 22012), dividing a float by zero triggers a floating-point exception. This is especially common in ratio and rate calculations where the denominator can become zero at runtime.

-- Problematic query

SELECT total_sales::float / total_orders::float AS avg_order_value

FROM daily_stats;



-- Safe fix using NULLIF

SELECT

    date,

    total_sales::float / NULLIF(total_orders, 0)::float AS avg_order_value

FROM daily_stats;

2. NaN Values in Arithmetic Operations

Data ingested from external systems, CSVs, or APIs may silently introduce NaN values into float columns. Once NaN participates in arithmetic, results become unpredictable and can trigger exceptions downstream.

-- Detect NaN values (NaN is the only value not equal to itself)

SELECT id, value

FROM sensor_readings

WHERE value != value;



-- Replace NaN with NULL safely

UPDATE sensor_readings

SET value = NULL

WHERE value != value;



-- Filter NaN in aggregations

SELECT

    device_id,

    AVG(value) FILTER (WHERE value = value) AS clean_avg

FROM sensor_readings

GROUP BY device_id;

3. Infinity Arithmetic Conflicts

Storing 'Infinity'::float or '-Infinity'::float is valid in PostgreSQL, but performing certain operations on them produces mathematically undefined results (e.g., Infinity - Infinity = NaN), which can cascade into a floating-point exception.

-- Check for Infinity values

SELECT id, measurement

FROM raw_data

WHERE measurement IN ('Infinity'::float, '-Infinity'::float);



-- Create a reusable safe conversion function

CREATE OR REPLACE FUNCTION safe_float(val float)

RETURNS float AS $$

BEGIN

    IF val IS NULL THEN RETURN NULL;

    ELSIF val != val THEN RETURN NULL;              -- catches NaN

    ELSIF abs(val) = 'Infinity'::float THEN RETURN NULL;  -- catches ±Inf

    ELSE RETURN val;

    END IF;

END;

$$ LANGUAGE plpgsql IMMUTABLE;



-- Use the function in queries

SELECT id, safe_float(measurement_a) + safe_float(measurement_b) AS safe_sum

FROM raw_data;

Quick Fix Solutions

• Use NULLIF(denominator, 0) on every division involving float types.


• Filter NaN with WHERE value = value before aggregating.


• Wrap suspicious float inputs with a sanitization function like safe_float() above.


• Use CASE WHEN guards in complex expressions to short-circuit dangerous operand values.

Prevention Tips

1. Enforce constraints at the table level using a custom domain:

CREATE DOMAIN safe_float AS float

CHECK (

    VALUE IS NULL

    OR (VALUE = VALUE

        AND VALUE != 'Infinity'::float

        AND VALUE != '-Infinity'::float)

);



CREATE TABLE measurements (

    id         SERIAL PRIMARY KEY,

    sensor_id  INT NOT NULL,

    value      safe_float    -- rejects NaN and Infinity on INSERT/UPDATE

);

2. Run periodic data quality checks:

-- Schedule this with pg_cron to catch bad data early

SELECT COUNT(*) AS problematic_rows

FROM measurements

WHERE value != value

   OR value = 'Infinity'::float

   OR value = '-Infinity'::float;

Applying these two practices consistently will prevent 22P01 errors from ever reaching production queries.

Related Errors

📖 Want a more detailed guide?

Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.