Understanding PostgreSQL’s LOG10() Function

In PostgreSQL, log10() is a mathematical function that returns the base 10 logarithm of its argument.

It was added in PostgreSQL 12 as a SQL-standard equivalent of the existing log() function.

There is a difference between the two functions though, because log() allows us to specify which base to use (although it defaults to base 10), whereas log10() uses base 10 only.

Syntax

This function can be used in the following ways:

log10 ( numeric )
log10 ( double precision )

So we can pass either a numeric or a double precision value.

Example

Here’s an example of how it works:

SELECT log10(37);

Result:

1.568201724066995

This is the same as using the log() function as follows:

SELECT log(37);

Result:

1.568201724066995

Calling log() like this uses base 10 as a default.

It’s also the same as doing this:

SELECT log(10, 37);

Result:

1.5682017240669950

In this example I explicitly specified that it should use base 10 (which is the default anyway).

Fractions

This example includes a fractional part in the argument.

SELECT log10(15.95);

Result:

1.2027606873931999

Expressions

You can include expressions, such as this.

SELECT log10(15 * 1.75);

Result:

1.4191293077419757

Passing a Negative Value

The argument must be greater than zero. Passing a negative value results in an error:

SELECT log10(-2);

Result:

ERROR:  cannot take logarithm of a negative number

Passing Zero

As mentioned, the argument must be greater than zero. Passing zero results in an error:

SELECT log10(0);

Result:

ERROR:  cannot take logarithm of zero

Passing Null

Passing null results in null:

SELECT log10(null);

Result:

null

Passing the Wrong Argument Type

Passing the wrong argument type results in an error:

SELECT log10('cat');

Result:

ERROR:  invalid input syntax for type double precision: "cat"
LINE 1: SELECT log10('cat');
^