How to Fix PostgreSQL Error: Division by Zero

The "Division by zero" error occurs when attempting to divide a number by zero in a SQL query. This is a mathematical error that PostgreSQL detects and reports to prevent undefined results.

Impact

This error causes query failures and can break reports, calculations, and analytical queries. While not critical, it indicates missing data validation or edge cases in query logic.

Common Causes

Dividing by column containing zero values
NULL values in denominator
Calculated denominators resulting in zero
Aggregations producing zero (SUM, COUNT, etc.)
Missing data validation
Edge cases in complex calculations
User input not validated

Troubleshooting and Resolution Steps

Identify the problematic query:

-- ERROR: division by zero
SELECT price / quantity FROM orders;

-- Find which rows have zero quantity
SELECT * FROM orders WHERE quantity = 0;

Use NULLIF to handle zero denominators:

-- WRONG: Direct division
SELECT price / quantity AS unit_price
FROM orders;

-- CORRECT: Returns NULL instead of error
SELECT price / NULLIF(quantity, 0) AS unit_price
FROM orders;

-- With default value using COALESCE
SELECT COALESCE(price / NULLIF(quantity, 0), 0) AS unit_price
FROM orders;

Use CASE statement for conditional logic:

-- Explicit zero check
SELECT
    price,
    quantity,
    CASE
        WHEN quantity = 0 THEN 0
        WHEN quantity IS NULL THEN NULL
        ELSE price / quantity
    END AS unit_price
FROM orders;

-- More complex business logic
SELECT
    total_sales,
    customer_count,
    CASE
        WHEN customer_count > 0 THEN total_sales / customer_count
        ELSE 0
    END AS average_per_customer
FROM sales_summary;

Handle NULL and zero together:

-- Comprehensive NULL and zero handling
SELECT
    revenue,
    users,
    CASE
        WHEN users IS NULL OR users = 0 THEN NULL
        ELSE revenue / users
    END AS revenue_per_user
FROM metrics;

-- Using COALESCE and NULLIF
SELECT
    revenue / NULLIF(COALESCE(users, 0), 0) AS revenue_per_user
FROM metrics;

Aggregation scenarios:

-- Calculate average order value
SELECT
    customer_id,
    SUM(order_total) AS total_spent,
    COUNT(*) AS order_count,
    -- Safe division
    SUM(order_total) / NULLIF(COUNT(*), 0) AS avg_order_value
FROM orders
GROUP BY customer_id;

-- Percentage calculations
SELECT
    category,
    sales_count,
    total_count,
    (sales_count * 100.0) / NULLIF(total_count, 0) AS percentage
FROM category_stats;

Create safe division function:

-- Create reusable safe division function
CREATE OR REPLACE FUNCTION safe_divide(numerator NUMERIC, denominator NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    IF denominator = 0 OR denominator IS NULL THEN
        RETURN NULL;
    END IF;
    RETURN numerator / denominator;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Usage
SELECT safe_divide(price, quantity) AS unit_price
FROM orders;

-- With default value
SELECT COALESCE(safe_divide(price, quantity), 0) AS unit_price
FROM orders;

Filter out problematic rows:

-- Only calculate for valid rows
SELECT
    price / quantity AS unit_price
FROM orders
WHERE quantity > 0;  -- Exclude zeros

-- In subquery
SELECT AVG(unit_price) FROM (
    SELECT price / quantity AS unit_price
    FROM orders
    WHERE quantity IS NOT NULL AND quantity != 0
) AS valid_orders;

Handle in application layer:

# Python example
import psycopg2
from psycopg2 import DataError

def calculate_unit_price(price, quantity):
    try:
        cursor.execute(
            "SELECT %s / %s AS unit_price",
            (price, quantity)
        )
        return cursor.fetchone()[0]
    except DataError as e:
        if 'division by zero' in str(e):
            return None  # or 0, or raise custom exception
        raise

# Or handle before query
def safe_division(numerator, denominator):
    if denominator is None or denominator == 0:
        return None
    return numerator / denominator

unit_price = safe_division(price, quantity)

Data validation constraints:

-- Prevent zero values at insertion
ALTER TABLE orders
ADD CONSTRAINT quantity_positive
CHECK (quantity > 0);

-- Or allow NULL but not zero
ALTER TABLE orders
ADD CONSTRAINT quantity_not_zero
CHECK (quantity IS NULL OR quantity != 0);

-- With custom error message (PostgreSQL 12+)
ALTER TABLE orders
ADD CONSTRAINT quantity_positive
CHECK (quantity > 0)
NOT VALID;  -- Don't check existing data

Complex calculations:

-- Nested calculations with multiple divisions
SELECT
    product_id,
    total_revenue,
    total_cost,
    unit_count,
    -- Multiple safe divisions
    (total_revenue - total_cost) / NULLIF(unit_count, 0) AS profit_per_unit,
    (total_revenue / NULLIF(total_cost, 0) - 1) * 100 AS profit_margin_pct,
    total_revenue / NULLIF(total_cost, 0) AS revenue_cost_ratio
FROM product_financials
WHERE unit_count IS NOT NULL;

Additional Information

Always use NULLIF when dividing by potentially zero values
Consider business logic - should result be NULL, 0, or different value?
NULL in division propagates (NULL / anything = NULL)
Use CHECK constraints to prevent invalid data
Modulo operator (%) also raises division by zero error
Integer division in PostgreSQL: 5/2 = 2 (truncated)
For decimal results, cast to NUMERIC or use decimals: 5.0/2

Frequently Asked Questions

Q: What's the difference between NULLIF and CASE for division?
A: NULLIF is more concise for simple zero checking. CASE is better for complex logic or multiple conditions.

Q: Should division by zero return NULL or 0?
A: Depends on business logic. NULL indicates "undefined/unknown", 0 indicates "zero value". Choose based on meaning in your context.

Q: Does COALESCE protect against division by zero?
A: No, COALESCE handles NULL values. Use NULLIF to convert zero to NULL before division.

Q: Can I configure PostgreSQL to return NULL instead of error?
A: No, division by zero always raises an error. Use NULLIF or CASE in queries.

Q: How do I handle percentage calculations?
A: Multiply by 100.0 (decimal) and use NULLIF: (value * 100.0) / NULLIF(total, 0)

Q: What about integer division?
A: Integer division truncates. For decimal: use CAST or ensure one operand is decimal: 5.0 / 2 or CAST(5 AS NUMERIC) / 2

Q: Can this error occur in aggregate functions?
A: Yes, if denominator aggregates to zero: AVG(price) / NULLIF(SUM(quantity), 0)

Q: How do I test for this error in my queries?
A: Use test data with zero values, NULL values, and empty result sets to ensure proper handling.