How to fix Floating point exception in PostgreSQL

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

-- 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.

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

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.

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

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.

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

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.

Verify that column values fall within acceptable ranges before use:

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:

SELECT * FROM sales WHERE amount = 0 OR amount IS NULL;

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

-- 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.