DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 22012 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 22012: Division by Zero

PostgreSQL error code 22012 (division_by_zero) is raised whenever the database engine encounters a division operation where the denominator evaluates to zero. Since dividing by zero is mathematically undefined, PostgreSQL immediately aborts the current transaction and returns this error. It can appear in simple arithmetic expressions, aggregate calculations, window functions, and any dynamic computation where a zero value unexpectedly ends up in the denominator.

Top 3 Causes

1. Direct Division by a Column That Contains Zero

The most common cause is dividing by a column that holds a zero value in one or more rows. Even if only a single row has a zero denominator, the entire query fails.

-- Triggers 22012 when quantity = 0 exists in the table
SELECT
    product_id,
    revenue / quantity AS unit_price
FROM sales;

-- Fix: use NULLIF to return NULL instead of erroring
SELECT
    product_id,
    revenue / NULLIF(quantity, 0) AS unit_price
FROM sales;

-- Fix with default fallback value
SELECT
    product_id,
    COALESCE(revenue / NULLIF(quantity, 0), 0) AS unit_price
FROM sales;
Enter fullscreen mode Exit fullscreen mode

2. Aggregate Function Result Used as Denominator

Using SUM(), COUNT(), or other aggregate results as a denominator is risky because certain groups may have no data or values that cancel out to zero.

-- Fails when a department has zero total sales
SELECT
    department_id,
    employee_id,
    individual_sales / SUM(total_sales) OVER (PARTITION BY department_id) AS ratio
FROM employee_sales;

-- Fix: wrap the window aggregate with NULLIF
SELECT
    department_id,
    employee_id,
    COALESCE(
        individual_sales
        / NULLIF(SUM(total_sales) OVER (PARTITION BY department_id), 0),
        0
    ) AS ratio
FROM employee_sales;
Enter fullscreen mode Exit fullscreen mode

3. Window Function or Dynamic Calculation in the Denominator

Using LAG(), LEAD(), or a dynamically computed expression as a denominator can produce zero when two consecutive values are identical or a computed difference cancels out.

-- Fails when consecutive prices are the same (LAG returns same value)
SELECT
    date,
    price,
    (price - LAG(price) OVER (ORDER BY date))
    / LAG(price) OVER (ORDER BY date) AS daily_return
FROM stock_prices;

-- Fix: protect the LAG result with NULLIF
SELECT
    date,
    price,
    COALESCE(
        (price - LAG(price) OVER (ORDER BY date))
        / NULLIF(LAG(price) OVER (ORDER BY date), 0),
        0
    ) AS daily_return
FROM stock_prices;
Enter fullscreen mode Exit fullscreen mode

Quick Fix: Reusable Safe Division Function

If division appears frequently across your codebase, encapsulate the logic in a helper function to keep queries clean and consistent.

CREATE OR REPLACE FUNCTION safe_divide(
    numerator   NUMERIC,
    denominator NUMERIC,
    fallback    NUMERIC DEFAULT NULL
)
RETURNS NUMERIC AS $$
BEGIN
    IF denominator IS NULL OR denominator = 0 THEN
        RETURN fallback;
    END IF;
    RETURN numerator / denominator;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Usage
SELECT product_id, safe_divide(revenue, quantity, 0) AS unit_price
FROM sales;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Add CHECK constraints at the table level to block zero values from being stored in columns that serve as denominators. This enforces data integrity at the database layer, independent of application-side validation. 
ALTER TABLE sales
ADD CONSTRAINT chk_quantity_positive CHECK (quantity > 0);
Enter fullscreen mode Exit fullscreen mode
  1. Adopt NULLIF as a team coding standard for every division expression in SQL. Incorporate a linting rule in your CI/CD pipeline (e.g., via sqlfluff) to flag any bare / operator used without NULLIF wrapping the denominator. Catching the issue at review time is far cheaper than debugging it in production.

Related Errors

  • 22003 numeric_value_out_of_range — Triggered when a division result exceeds the target data type's range.
  • 22P02 invalid_text_representation — Can chain with 22012 in dynamic queries where string-to-number casting fails before division occurs.
  • 23514 check_violation — Intentionally raised by the CHECK constraint prevention strategy described above, effectively stopping bad data before it can cause a division by zero.

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

Top comments (0)