How to fix Floating point exception in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 26, 2025· 127 views

A floating point exception in PostgreSQL typically occurs due to division by zero or invalid arithmetic operations. This can be prevented by adding guards in your SQL queries to check for zero denominators before performing division.

Quick fix

Fixes 22P01

-- Instead of this (will error if amount is 0):
SELECT total / amount FROM sales;

-- Use this:
SELECT total / NULLIF(amount, 0) FROM sales;
  1. Guard division with NULLIF to prevent division by zero
  2. Use CASE statements for conditional logic
  3. Use COALESCE to provide a fallback value
  4. Switch to NUMERIC type for exact calculations
postgresql — error
-- Instead of this (will error if amount is 0):
SELECT total / amount FROM sales;
-- Use this:
SELECT total / NULLIF(amount, 0) FROM sales;
Floating point exception

What this error means

A floating point exception (error code 22P01 in PostgreSQL) is a fatal arithmetic error that occurs when the database engine encounters an invalid mathematical operation. This includes division by zero, overflow (result too large to represent), underflow (result too small), or other undefined mathematical operations. Although the name suggests floating-point operations, the error can occur with any numeric type including integers. The error causes the current statement to fail and typically rolls back the active transaction.

Typical symptoms

  • Queries fail with error message "ERROR: floating point exception"
  • Database operations abort unexpectedly mid-execution
  • Transactions rollback when dividing numeric columns or expressions
  • Calculation-heavy queries fail intermittently or predictably based on data values
  • Database logs show SIGFPE (signal 8) before the error message

Common causes

  1. Division by zero: A numeric column or subquery result equals zero in a division operation
  2. Overflow: Numeric calculation results exceed the maximum representable value (e.g., REAL: ~1E+37, DOUBLE PRECISION: ~1E+308)
  3. Underflow: Numeric result is too small to represent as distinct from zero
  4. Invalid data types: Using REAL or DOUBLE PRECISION with values outside safe ranges
  5. Modulo operations: Using the % operator with a zero divisor
  6. Aggregate function overflow: SUM() or other aggregates exceeding type limits

How to fix "Floating point exception"

1Guard division with NULLIF to prevent division by zero

Use the NULLIF() function to return NULL when the divisor equals zero, which avoids the exception entirely:

sql
-- Instead of this (will error if amount is 0):
SELECT total / amount FROM sales;

-- Use this:
SELECT total / NULLIF(amount, 0) FROM sales;

This approach leverages the fact that dividing by NULL returns NULL rather than an error.

2Use CASE statements for conditional logic

For more complex logic, wrap the division in a CASE statement:

sql
SELECT
  CASE
    WHEN amount = 0 THEN 0
    ELSE total / amount
  END AS ratio
FROM sales;

This gives you control over what value to return when division is invalid, such as 0, NULL, or a default value.

3Use COALESCE to provide a fallback value

When NULLIF() returns NULL, use COALESCE() to supply a default:

sql
SELECT
  COALESCE(total / NULLIF(amount, 0), 0) AS ratio
FROM sales;

This ensures your result is always a number rather than potentially NULL, useful when you need a specific default for undefined calculations.

4Switch to NUMERIC type for exact calculations

If the error occurs with currency or financial calculations, use NUMERIC type instead of REAL or DOUBLE PRECISION:

sql
ALTER TABLE sales
ALTER COLUMN amount TYPE NUMERIC(10,2);

NUMERIC provides exact decimal arithmetic without floating-point rounding errors. It also has a larger safe range for calculations.

5Check data ranges and add validation constraints

Verify that column values fall within acceptable ranges before use:

sql
ALTER TABLE sales
ADD CONSTRAINT amount_positive CHECK (amount > 0);

Preventing zero or invalid values from entering the database eliminates the root cause. For existing data, first identify problematic rows:

sql
SELECT * FROM sales WHERE amount = 0 OR amount IS NULL;
6Test the fix with your problematic data

After applying guards, test with the exact queries and data that caused the error:

sql
-- Identify the problematic query
SELECT total / NULLIF(amount, 0) AS ratio
FROM sales
WHERE amount = 0;  -- This now returns NULL instead of error

Verify the result is NULL, 0, or your chosen default rather than an exception.

Advanced notes

Understanding the underlying cause helps prevent recurrence:

Overflow Protection: For aggregate functions like SUM(), cast to a larger type if needed: SUM(amount)::DOUBLE PRECISION / COUNT(*)

Modulo Operations: The % operator also fails with zero divisors. Guard it the same way: column % NULLIF(divisor, 0)

Performance: CASE statements can be slower than simple NULLIF for large datasets. Profile both approaches on your data.

Type Selection: NUMERIC is slower but exact (use for money). DOUBLE PRECISION is faster but subject to rounding and overflow (use for scientific data). REAL is fastest but least precise.

Application-Level Handling: If division is application logic, consider moving the check to your code rather than the database to reduce round-trips and errors.

Compiler Bugs: Historically, PostgreSQL on some systems could report "floating point exception" for division by zero on integer types due to compiler bugs. Update PostgreSQL if the error persists on simple integer division.

Frequently asked questions

What does "22P01" mean?
A floating point exception (error code 22P01 in PostgreSQL) is a fatal arithmetic error that occurs when the database engine encounters an invalid mathematical operation. This includes division by zero, overflow (result too large to represent), underflow (result too small), or other undefined mathematical operations. Although the name suggests floating-point operations, the error can occur with any numeric type including integers.
How do I fix "22P01"?
A floating point exception in PostgreSQL typically occurs due to division by zero or invalid arithmetic operations. This can be prevented by adding guards in your SQL queries to check for zero denominators before performing division.
What causes "22P01"?
Division by zero: A numeric column or subquery result equals zero in a division operation Overflow: Numeric calculation results exceed the maximum representable value (e.g., REAL: ~1E+37, DOUBLE PRECISION: ~1E+308) Underflow: Numeric result is too small to represent as distinct from zero
Is the "22P01" error serious?
It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading