How to fix Division by zero in PostgreSQL

What this error means

The PostgreSQL error code 22012 "division_by_zero" occurs when any numeric expression attempts to divide by zero, either a literal zero value or a column that evaluates to zero. This is a strict mathematical rule enforced by the database engine to prevent undefined calculations. When PostgreSQL encounters division by zero, it immediately stops query execution and raises an error with SQLSTATE 22012. This error cannot be suppressed with database settings—you must handle the condition in your SQL logic or application code. The error typically occurs during calculations, financial reports, rate computations, or analytics queries where denominator values aren't pre-validated. Unlike some databases that return NULL or Infinity for division by zero, PostgreSQL treats this as an error condition. NULL values divided by any number correctly return NULL, but dividing by NULL also returns NULL rather than an error—only literal or computed zeros trigger the 22012 error.

Typical symptoms

• Query fails with error "ERROR: division by zero" and SQLSTATE 22012
• Application crashes when executing financial or rate calculation queries
• Reports or analytics queries abort unexpectedly on certain data subsets
• Transaction rolls back when division by zero occurs
• Error appears intermittently, only for specific data values
• Calculations that should work with some rows fail unexpectedly

Common causes

1. Dividing by a column value that contains zero for one or more rows
2. Computing a divisor that evaluates to zero (e.g., (x - x), (y * 0))
3. Rate calculations like (success_count / total_attempts) where total_attempts can be zero
4. Financial calculations like (profit / expenses) where expenses may be zero
5. Aggregate functions that return zero (e.g., COUNT(*) in HAVING clause)
6. Subquery results that evaluate to zero used as denominators
7. Using user-supplied values as divisors without validation

How to fix "Division by zero"

-- Example queries that cause division by zero
SELECT user_id, total_sales / total_orders FROM user_stats;
-- ERROR: division by zero (when total_orders is 0)

SELECT profit / expenses FROM financial_reports;
-- ERROR: division by zero (when expenses is 0)

SELECT (a - b) / (c - d) FROM calculations;
-- ERROR: division by zero (when c equals d)

-- Find rows where divisor is zero
SELECT id, total_orders, total_sales
FROM user_stats
WHERE total_orders = 0;

-- Count how many rows are affected
SELECT COUNT(*) as affected_rows
FROM financial_reports
WHERE expenses = 0 OR expenses IS NULL;

-- Check for derived zero values
SELECT id, (col_a - col_b) as divisor
FROM my_table
WHERE (col_a - col_b) = 0;

-- Basic NULLIF approach
SELECT
  user_id,
  total_sales / NULLIF(total_orders, 0) as avg_sale
FROM user_stats;

-- With COALESCE to provide a default
SELECT
  user_id,
  COALESCE(total_sales / NULLIF(total_orders, 0), 0) as avg_sale
FROM user_stats;

-- For multiple divisions
SELECT
  id,
  revenue / NULLIF(expenses, 0) as profit_ratio,
  profit / NULLIF(investment, 0) as roi
FROM financial_data;

-- Filter out zero divisors
SELECT
  user_id,
  total_sales / total_orders as avg_sale
FROM user_stats
WHERE total_orders > 0;

-- Multiple conditions
SELECT
  id,
  revenue / expenses as profit_margin
FROM financial_reports
WHERE expenses > 0
  AND revenue > 0
  AND report_year = 2024;

-- With aggregate functions
SELECT
  category,
  SUM(revenue) / SUM(expenses) as total_margin
FROM sales_data
WHERE expenses > 0
GROUP BY category;

-- Return NULL for zero divisors
SELECT
  user_id,
  CASE
    WHEN total_orders > 0 THEN total_sales / total_orders
    ELSE NULL
  END as avg_sale
FROM user_stats;

-- Return a default value instead of NULL
SELECT
  id,
  CASE
    WHEN expenses > 0 THEN revenue / expenses
    ELSE 0  -- or any default value
  END as profit_margin
FROM financial_reports;

-- Multi-level logic
SELECT
  id,
  category,
  CASE
    WHEN divisor > 0 THEN numerator / divisor
    WHEN divisor = 0 THEN NULL
    ELSE -1  -- Flag error conditions
  END as result
FROM calculations;

-- Ensure divisor is at least 1
SELECT
  user_id,
  total_sales / GREATEST(total_orders, 1) as avg_sale
FROM user_stats;

-- With multiple columns
SELECT
  product_id,
  total_revenue / GREATEST(total_units, 1) as avg_price
FROM inventory;

-- In aggregate queries
SELECT
  category,
  SUM(amount) / GREATEST(COUNT(*), 1) as avg_amount
FROM transactions
GROUP BY category;

-- Add CHECK constraint to ensure positive values
ALTER TABLE user_stats
ADD CONSTRAINT chk_positive_orders
CHECK (total_orders >= 0);

-- Use DEFAULT values
ALTER TABLE financial_reports
ALTER COLUMN expenses SET DEFAULT 1;

-- Add NOT NULL constraints where zero shouldn't exist
ALTER TABLE calculations
ALTER COLUMN divisor SET NOT NULL;

-- Create index for filtered queries
CREATE INDEX idx_user_stats_active
ON user_stats (user_id)
WHERE total_orders > 0;

Advanced notes

-- Create materialized view to pre-calculate safe ratios
CREATE MATERIALIZED VIEW user_statistics AS
SELECT
  user_id,
  total_sales / GREATEST(total_orders, 1) as avg_sale,
  COUNT(*) as transaction_count
FROM user_stats
GROUP BY user_id;

-- Dangerous: SUM() could be 0
SELECT category, SUM(amount) / SUM(quantity) FROM sales GROUP BY category;

-- Safe: NULLIF protects aggregates
SELECT
  category,
  SUM(amount) / NULLIF(SUM(quantity), 0)
FROM sales
GROUP BY category;

-- Derived zero: (a - a) = 0
SELECT value / NULLIF(a - a, 0) FROM table;

-- Practical example: percentage change where old = new
SELECT
  (new_value - old_value) / NULLIF(old_value, 0) as pct_change
FROM metrics;

Frequently asked questions

What does "22012" mean?

The PostgreSQL error code 22012 "division_by_zero" occurs when any numeric expression attempts to divide by zero, either a literal zero value or a column that evaluates to zero. This is a strict mathematical rule enforced by the database engine to prevent undefined calculations. When PostgreSQL encounters division by zero, it immediately stops query execution and raises an error with SQLSTATE 22012.

How do I fix "22012"?

PostgreSQL error 22012 occurs when an SQL query attempts to divide a numeric value by zero. This runtime error stops query execution and rolls back the transaction. Use NULLIF(), CASE statements, or data filtering to prevent this error.

What causes "22012"?

Dividing by a column value that contains zero for one or more rows Computing a divisor that evaluates to zero (e.g., (x - x), (y * 0)) Rate calculations like (success_count / total_attempts) where total_attempts can be zero

Is the "22012" error serious?

It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Related errors

• Duplicate alias

  How to fix "Duplicate alias" in PostgreSQL

  MEDIUM

• Disk full

  How to fix "Disk full" in PostgreSQL

  CRITICAL

• Duplicate column

  How to fix "Duplicate column" in PostgreSQL

  MEDIUM

• Deadlock detected

  How to fix "Deadlock detected" in PostgreSQL

  HIGH

• Invalid password

  How to fix "Invalid password" in PostgreSQL

  HIGH

• FATAL: no pg_hba.conf entry for host

  How to fix 'FATAL: no pg_hba.conf entry for host' in PostgreSQL

  HIGH

Official resources & further reading

• 📚PostgreSQL Official Documentation
• 📖
  PostgreSQL Error Codes (Appendix A)(Official Docs)
• GeeksforGeeks - How to Avoid Division By Zero in PostgreSQL
• LearnSQL.com - How to Handle Divide by Zero in SQL
• Database Docs - Resolving Division by Zero in PostgreSQL
• pganalyze - Division by zero Error Analysis