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

Code	Name	Notes
22012	division_by_zero	Integer division by zero; sibling of 22P01
22003	numeric_value_out_of_range	Overflow on numeric/integer types
22000	data_exception	Parent class covering all 22xxx data 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.