PostgreSQL 2201E Error: Causes and Solutions Complete Guide

PostgreSQL Error 2201E: Invalid Argument for Logarithm

PostgreSQL error 2201E (invalid_argument_for_logarithm) is thrown when a logarithmic function — LN(), LOG(), or LOG10() — receives an argument that is zero or negative. Mathematically, logarithms are only defined for strictly positive real numbers, so PostgreSQL enforces this domain rule at runtime. This error commonly surfaces in analytical queries involving financial data, scientific calculations, or any numeric pipeline where input values aren't fully validated.

---

Top 3 Causes

1. Passing Zero or Negative Values Directly

The most straightforward cause: calling a log function with a literal 0 or a negative number, or with a column that happens to contain such values.

-- These will all throw ERROR 2201E
SELECT LN(0);
SELECT LOG(10, -5);
SELECT LN(price) FROM products;  -- fails if any price = 0 or price < 0

-- Safe fix using NULLIF
SELECT LN(NULLIF(price, 0)) FROM products WHERE price > 0;

-- Safe fix using CASE
SELECT
    product_id,
    CASE WHEN price > 0 THEN LN(price) ELSE NULL END AS ln_price
FROM products;

2. Aggregated or Computed Values Resulting in Zero/Negative

Even if raw columns look safe, derived values from SUM(), subtraction, or other arithmetic can become zero or negative at runtime with certain data combinations.

-- Dangerous: profit could be zero or negative
SELECT
    department,
    LN(SUM(revenue) - SUM(expenses)) AS log_profit
FROM financials
GROUP BY department;

-- Safe fix: filter with HAVING
SELECT
    department,
    LN(SUM(revenue) - SUM(expenses)) AS log_profit
FROM financials
GROUP BY department
HAVING (SUM(revenue) - SUM(expenses)) > 0;

-- Safe fix: use CASE for NULL fallback
SELECT
    department,
    CASE
        WHEN (SUM(revenue) - SUM(expenses)) > 0
        THEN LN(SUM(revenue) - SUM(expenses))
        ELSE NULL
    END AS log_profit
FROM financials
GROUP BY department;

3. Missing Input Validation on External or User-Supplied Data

When data originates from user input, CSV imports, or API feeds, values can be unexpectedly zero or negative without prior sanitization.

-- Audit your data before applying log functions
SELECT id, metric_value,
    CASE
        WHEN metric_value IS NULL THEN 'NULL'
        WHEN metric_value <= 0   THEN 'Invalid (<= 0)'
        ELSE 'OK'
    END AS validation_status
FROM raw_input_data
WHERE metric_value <= 0 OR metric_value IS NULL;

-- Create a reusable safe wrapper function
CREATE OR REPLACE FUNCTION safe_ln(val NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    IF val IS NULL OR val <= 0 THEN
        RETURN NULL;
    END IF;
    RETURN LN(val);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Use it safely anywhere
SELECT safe_ln(user_input_value) FROM user_data;

---

Quick Fix Summary

Scenario	Fix
Column might be 0	LN(NULLIF(col, 0))
Column might be negative	CASE WHEN col > 0 THEN LN(col) ELSE NULL END
Aggregated result	Add HAVING sum_col > 0
Repeated usage	Create a safe_ln() wrapper function

---

Prevention Tips

1. Enforce Positive Values with CHECK Constraints

Add CHECK constraints at the table level so invalid values never enter the database in the first place.

CREATE TABLE metrics (
    id      SERIAL PRIMARY KEY,
    value   NUMERIC CHECK (value > 0)
);

-- Or add to an existing table
ALTER TABLE metrics
    ADD CONSTRAINT chk_value_positive CHECK (value > 0);

2. Encapsulate Log Logic in Safe Views or Functions

Abstract all logarithmic calculations into views or stored functions that include built-in guards. This ensures every consumer of the logic is automatically protected without needing to rewrite defensive conditions each time.

CREATE OR REPLACE VIEW safe_metric_logs AS
SELECT
    id,
    value,
    CASE WHEN value > 0 THEN LN(value)      ELSE NULL END AS ln_value,
    CASE WHEN value > 0 THEN LOG(10, value) ELSE NULL END AS log10_value
FROM metrics;

---

Related Errors

• 22012 division_by_zero — Similar domain violation, triggered by dividing by zero.
• 2201F invalid_argument_for_power_function — Invalid input to power/exponent functions.
• 2201G invalid_argument_for_width_bucket_function — Bad arguments passed to WIDTH_BUCKET().

---

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