PostgreSQL 22012 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22012: Division by Zero — Causes, Fixes & Prevention

PostgreSQL error code 22012 (division by zero) is raised whenever the database engine encounters a division operation where the denominator evaluates to zero. This error belongs to the SQL standard class "22" (Data Exception) and can surface in simple arithmetic, aggregate functions, and window functions alike. Left unhandled, it will immediately terminate the query and roll back any open transaction, making it critical to address proactively.

---

Top 3 Causes

1. Column Values That Are Zero

The most common cause is dividing by a column that contains zero rows in production data, even though test data looked fine.

-- Triggers error when quantity = 0
SELECT total_amount / quantity AS unit_price
FROM sales;

-- ERROR:  division by zero

2. Aggregate or Window Function Results Equaling Zero

When using COUNT(), SUM(), or similar functions as a denominator, certain groups or partitions may produce a zero result.

-- Dangerous: COUNT(*) could be 0 for some categories
SELECT category, SUM(revenue) / COUNT(*) AS avg_revenue
FROM orders
GROUP BY category;

-- Dangerous in window context
SELECT
    employee_id,
    salary / SUM(salary) OVER (PARTITION BY department_id) AS ratio
FROM employees;

3. Unvalidated Application or Dynamic Query Input

When application code binds a calculated or user-supplied value as a denominator without a zero check, the database throws the error.

-- If :user_input is 0, this will fail at runtime
SELECT total_sales / :user_input AS result
FROM summary;

---

Quick Fix Solutions

Use NULLIF() — the most idiomatic PostgreSQL solution. It returns NULL when the denominator equals zero, preventing the error entirely.

-- Safe division with NULLIF
SELECT total_amount / NULLIF(quantity, 0) AS unit_price
FROM sales;

-- Return 0 instead of NULL using COALESCE
SELECT COALESCE(total_amount / NULLIF(quantity, 0), 0) AS unit_price
FROM sales;

-- Safe aggregate query
SELECT
    category,
    SUM(revenue) / NULLIF(COUNT(*), 0) AS avg_revenue
FROM orders
GROUP BY category;

-- Safe window function
SELECT
    employee_id,
    salary / NULLIF(SUM(salary) OVER (PARTITION BY department_id), 0) AS ratio
FROM employees;

-- Reusable wrapper function
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 safe_divide(total_price, item_count, 0) AS avg_item_price
FROM orders;

---

Prevention Tips

1. Standardize NULLIF() in all division operations.
Add a team coding convention and code review checklist item requiring NULLIF(denominator, 0) for every / operation. Consider deploying a SQL linter that flags bare division operators without NULLIF.

2. Enforce data constraints at the schema level.
Add CHECK constraints to columns that must never be zero, catching bad data at insertion time rather than at query time.

-- Prevent zero at the table level
CREATE TABLE sales (
    id          SERIAL PRIMARY KEY,
    quantity    INT    NOT NULL CHECK (quantity > 0),
    total_price NUMERIC(12,2) NOT NULL
);

-- Add constraint to existing table
ALTER TABLE sales
ADD CONSTRAINT chk_quantity_positive CHECK (quantity > 0);

Always include boundary-value test cases (0, NULL, negative numbers) in your unit and integration test suites to catch division by zero before code reaches production.

---

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