SQL LOG: Base-10 and Arbitrary-Base Logarithms, and the MySQL Natural-Log Trap

LOG returns the logarithm of a number — the power to which the base must be raised to produce the argument. In PostgreSQL the function has two forms: LOG(x) for base 10 and LOG(b, x) for an arbitrary base.

Two forms of LOG in PostgreSQL

Without a second argument, LOG computes the base-10 logarithm. Pass two arguments and the first one becomes the base.

SELECT
  LOG(100)    AS a,   -- 2   (10^2 = 100)
  LOG(1000)   AS b,   -- 3   (10^3 = 1000)
  LOG(2, 8)   AS c,   -- 3   (2^3 = 8)
  LOG(2, 1024) AS d;  -- 10  (2^10 = 1024)

Key properties:

• LOG(x) is the logarithm base 10, not the natural log.
• LOG(b, x) reads as "log of x to base b".
• The result type is numeric, so precision is high but it is slower than double precision.
• The argument must be strictly positive: LOG(0) and LOG(-5) raise an error rather than returning NULL.

The main trap: MySQL LOG is the natural log

This is the most dangerous spot when porting queries. In MySQL LOG(x) with a single argument is the natural logarithm (base e), not base 10. The same call returns different numbers in different engines.

-- PostgreSQL: LOG(100) = 2     (base 10)
-- MySQL:      LOG(100) = 4.605  (base e, natural log)
SELECT LOG(100) AS surprise;

To keep code dialect-independent, be explicit:

• Base-10 log: PostgreSQL LOG(x), MySQL LOG10(x). The LOG10 function exists in both and is unambiguous.
• Natural log: use LN(x) — it means base e in both PostgreSQL and MySQL.
• Arbitrary base: PostgreSQL LOG(b, x), MySQL has the same LOG(b, x) syntax with the same argument order, so it ports cleanly.

Gotcha: never rely on single-argument LOG(x) in cross-engine code. Write LOG10(x) for base 10 and LN(x) for base e, and the meaning is the same everywhere.

Log scales and digit counting

Logarithms compress values that grow over orders of magnitude: order amounts, event counts, file sizes. On a log scale "10 and 100" sit as far apart as "100 and 1000".

SELECT
  FLOOR(LOG(amount))::int AS magnitude,   -- 0, 1, 2, 3 ...
  COUNT(*)                AS orders
FROM orders
WHERE status = 'paid' AND amount > 0
GROUP BY FLOOR(LOG(amount))::int
ORDER BY magnitude;

Here orders are grouped by order of magnitude: 0 is 1..9, 1 is 10..99, 2 is 100..999. A related trick is counting the digits of an integer: the digit count equals FLOOR(LOG10(n)) + 1.

SELECT
  id,
  salary,
  FLOOR(LOG(salary))::int + 1 AS digits   -- digits in the salary
FROM employees
WHERE salary > 0
ORDER BY salary DESC;

Gotcha: always filter out non-positive values with WHERE amount > 0 or NULLIF. A single amount = 0 or a negative refund will crash the whole query with a domain error. A guard: LOG(NULLIF(amount, 0)) turns zero into NULL instead of an error.

Relation to LN and changing the base

LOG and LN are relatives: both compute a logarithm, only the base differs. LN(x) is base e, LOG(x) is base 10, and LOG(b, x) is base b. Any base can be expressed through them with the change-of-base formula.

SELECT
  LN(100)            AS natural_log,   -- 4.605
  LOG(100)           AS log10,         -- 2
  LN(8) / LN(2)      AS log2_via_ln,   -- 3
  LOG(2, 8)          AS log2_builtin;  -- 3

Dialect notes:

• PostgreSQL: LOG(x) is base 10; LOG(b, x) is arbitrary; LN(x) is natural.
• MySQL: LOG(x) is natural (base e); for base 10 use LOG10(x); LOG2(x) also exists.
• ClickHouse: log(x) and ln(x) are natural; use log10(x) for base 10 and log2(x) for base 2. There is no log(b, x) form, so use log(x) / log(b).

Remember three things: in PostgreSQL LOG(x) is base 10, in MySQL the same LOG(x) is the natural log, and to avoid confusion write LOG10 and LN explicitly.