PostgreSQL Invalid Argument for Logarithm (SQLSTATE 22020)

When you pass zero or a negative number to a logarithmic function in PostgreSQL, you will see an error like:

ERROR:  cannot take logarithm of zero
SQLSTATE: 22020

or

ERROR:  cannot take logarithm of a negative number
SQLSTATE: 22020

This is SQLSTATE 22020, condition name invalid_argument_for_log. It indicates that the argument supplied to LOG(), LOG10(), or LN() is outside the mathematically valid domain for those functions (which require strictly positive input).

What This Error Means

SQLSTATE 22020 belongs to error class 22 — Data Exception — a broad class covering constraint violations related to data values rather than schema structure. Other members of this class include divide-by-zero (22012), numeric value out of range (22003), and invalid argument for power (2201F). These errors share the property that the problem lies in the runtime value of data, not in the query structure itself.

PostgreSQL raises 22020 inside its numeric math functions (log, ln, log10) when the input value is <= 0. Mathematically, logarithms are only defined for positive real numbers: log(0) diverges to negative infinity, and log(negative) is undefined in the reals. PostgreSQL enforces this strictly rather than returning NULL or NaN.

The error aborts the current statement and, if inside an explicit transaction, leaves that transaction in an aborted state. Any further statements in the transaction block will fail with ERROR: current transaction is aborted until a ROLLBACK (or ROLLBACK TO SAVEPOINT) is issued.

Common Causes

1. Computing a log on a column that can contain zero or negative values. A numeric column such as revenue, quantity, or delta may have values that are non-positive. Using LOG(revenue) directly without a guard will fail on any such row.

2. Log-scaling data for analytics or normalization. Log-transformations are common in analytics queries (e.g., log-scaling a metric before inserting into a reporting table). If the source data has not been filtered or cleansed, zero or negative values will trigger this error.

3. Off-by-one or sign errors in computed expressions. An expression like LOG(col - 1) will fail whenever col = 1 (producing log(0)) or col < 1. Similarly, LOG(-col) when col is positive will fail.

4. Aggregate results passed to LOG. When chaining LOG(SUM(...)) or LOG(AVG(...)), the aggregate may produce zero or a negative result for certain filter conditions, causing the error at the outer function call.

How to Fix invalid_argument_for_log

1. Filter out invalid rows with a WHERE clause.

   SELECT LOG(revenue)
   FROM sales
   WHERE revenue > 0;
   

2. Use a CASE expression or NULLIF to return NULL instead of raising an error.

   -- Return NULL for non-positive values
   SELECT CASE WHEN revenue > 0 THEN LOG(revenue) ELSE NULL END AS log_revenue
   FROM sales;
   
   -- Alternatively, using NULLIF to coerce zero to NULL before taking the log
   -- (does not handle negatives — add a GREATEST guard for that)
   SELECT LOG(NULLIF(revenue, 0)) AS log_revenue
   FROM sales;
   

3. Clamp or shift the value to ensure positivity.

   If domain knowledge allows it, shift values so the minimum is above zero:

   -- Add a small epsilon to avoid log(0)
   SELECT LOG(revenue + 0.0001) AS log_revenue
   FROM sales;
   
   -- Or use GREATEST to enforce a floor
   SELECT LOG(GREATEST(revenue, 0.0001)) AS log_revenue
   FROM sales;
   

4. Validate inputs before passing to the function in application code.

   If values originate from application logic or user input, validate that they are positive before constructing the query:

   if value <= 0:
       raise ValueError("Log requires a positive argument")
   cursor.execute("SELECT LOG(%s)", (value,))
   

5. Wrap in a savepoint if you need partial-failure tolerance inside a transaction.

   BEGIN;
   SAVEPOINT before_log;
   SELECT LOG(some_col) FROM my_table;
   -- If this fails with 22020, roll back to the savepoint
   ROLLBACK TO SAVEPOINT before_log;
   COMMIT;
   

Additional Information

• SQLSTATE 22020 has been part of the SQL standard and PostgreSQL since early versions; there is no version-specific behavior change to be aware of.
• Related SQLSTATE codes in class 22:
  • 22012 — division_by_zero
  • 2201E — invalid_argument_for_log (alternative; 22020 is the canonical code PostgreSQL uses)
  • 2201F — invalid_argument_for_power (raised when, for example, 0 ^ negative_exponent is computed)
  • 2201G — invalid_argument_for_width_bucket_function
  • 22003 — numeric_value_out_of_range
• Most PostgreSQL drivers (libpq, psycopg2, asyncpg, pg, JDBC) surface this as a DatabaseError or equivalent with the SQLSTATE code accessible on the exception object. In psycopg2, check e.pgcode == '22020'; in asyncpg, inspect asyncpg.NumericValueOutOfRangeError or the raw sqlstate attribute.
• There are no performance implications specific to this error — it aborts a single statement and is not indicative of index bloat, lock contention, or any systemic issue.

Frequently Asked Questions

Why does PostgreSQL raise an error instead of returning NULL or -Infinity?

The SQL standard mandates raising a data exception for mathematically undefined operations. PostgreSQL follows this strictly for LOG and LN. If you prefer a NULL result over an error, use a CASE expression or NULLIF to coerce the invalid value to NULL before calling the function.

Does this error affect the entire transaction or just the statement?

It aborts the current statement. If you are inside an explicit BEGIN/COMMIT block, the transaction is also marked as aborted and must be rolled back before you can continue. In autocommit mode (the default for most clients and drivers), only the single statement is lost.

My column only has positive values but I'm still getting this error — why?

Check for expressions that can produce a non-positive intermediate value, such as LOG(col - n) where col equals n, or LOG(col1 / col2) where the division result rounds to zero. Also verify that no UPDATE or concurrent write has introduced non-positive values since you last validated the data.

Is there a way to make PostgreSQL return NULL silently for invalid log arguments without changing every query?

PostgreSQL does not offer a server-level setting to suppress 22020 as a NULL result. The closest approach is to create a wrapper function:

CREATE OR REPLACE FUNCTION safe_log(x numeric)
RETURNS numeric LANGUAGE sql IMMUTABLE AS $$
  SELECT CASE WHEN x > 0 THEN LOG(x) ELSE NULL END;
$$;

Then use safe_log(col) in place of LOG(col) throughout your queries.