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');
^