PostgreSQL 22012 Error: Causes and Solutions Complete Guide

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;

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;

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;

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;

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);

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.