The mathematical functions provided for PostgreSQL operate on a variety of numeric data types, and generally return a
value of the same type as the function's arguments. They can perform many useful and common arithmetic and trigonometric
operations; Table 5-9 provides an overview of some of the most common mathematical
functions in PostgreSQL.
Table 5-9. Mathematical functions in PostgreSQL
|
Function
|
Description
|
|
abs(
x
)
|
Returns the absolute value of
x
|
|
acos(
x
)
|
Returns the inverse cosine of
x
|
|
asin(
x
)
|
Returns the inverse sine of
x
|
|
atan(
x
)
|
Returns the inverse tangent of
x
|
|
atan2(
x
,
y
)
|
Returns the inverse tangent of the quotient of
x
and
y
|
|
cbrt(
x
)
|
Returns the cube root of
x
|
|
ceil(
x
)
|
Returns the smallest whole integer not less than argument (rounds up)
|
|
cos(
x
)
|
Returns the cosine of
x
|
|
cot(
x
)
|
Returns the cotangent of
x
|
|
degrees(
r
)
|
Returns degrees from radians
r
|
|
exp(
x
)
|
Returns the
e
constant (2.71828...), to the power of
x
|
|
floor(
x
)
|
Returns the largest whole integer not greater than
x
(rounds down)
|
|
ln(
x
)
|
Returns the natural logarithm of
x
(the inverse of the exp() function)
|
|
log(
b
,
x
)
|
Returns the base
b
logarithm of
x
|
|
log(
x
)
|
Returns the base 10 logarithm of
x
|
|
mod(
x
,
y
)
|
Returns the remainder (modulus) when dividing
x
/
y
|
|
pi()
|
Returns the
pi
constant (3.14159...)
|
|
pow(
x
,
y
)
|
Returns value of
x
to the exponential power of
y
|
|
radians(
d
)
|
Returns radian equivalent to
d
degrees
|
|
random()
|
Returns a pseudo-random value from 0.0 to 1.0
|
|
round(
x
)
|
Returns
x
rounded to the nearest whole integer
|
|
round(
x
,
s
)
|
Returns the value of
x
, optionally rounded to
s
decimal places
|
|
sin(
x
)
|
Returns the sine of
x
|
|
sqrt(
x
)
|
Returns the square root of
x
|
|
tan(
x
)
|
Returns the tangent of
x
|
|
trunc(
x
)
|
Returns the value of
x
, with any digits past the decimal point truncated
|
|
trunc(
x
,
s
)
|
Returns the value of
x
, with any digits past
s
decimal points truncated
|
The following sections elaborate on each of the functions described in Table 5-9,
detailing required arguments, data types, and functionality. Note that while a function will usually only accept one of
a set of data types as its arguments, PostgreSQL will attempt to implicitly convert supplied arguments to the required
types, if necessary. If an implicit type conversion fails, PostgreSQL will supply the appropriate error message, and you
may need to use an explicit type conversion. See Chapter 3 for more information on explicitly converting
types.
abs(
x
)
The abs() function accepts a single numeric argument
x
, and
returns its absolute value (distance from zero). It therefore has no effect on positive numbers, but inverts the sign of
a negative number to a positive number.
It can accept an argument which is of any of the numeric data types (numeric,
bigint, smallint,
real, or double precision), and returns the result in
form of the same data type which was passed to it.
Example
testdb=#
SELECT abs(100) AS abs_positive,
testdb-#
abs(-100) AS abs_negative;
abs_positive | abs_negative
--------------+--------------
100 | 100
(1 row)
acos(
x
)
The acos() function accepts a valid cosine, and returns the inverse (or arc)
cosine of the double precision argument
x
(between –1 and 1) passed
to it. This effectively returns the inverse of the cos() function. The result is a
double precision value of an angle, in radians, between 0 and
pi
.
Example
testdb=#
SELECT acos(1), acos(0), acos(-1),
testdb-#
acos(cos(1)) AS inverse_example;
acos | acos | acos | inverse_example
------+-----------------+------------------+-----------------
0 | 1.5707963267949 | 3.14159265358979 | 1
(1 row)
asin(
x
)
The asin() function returns the inverse (or arc) sine of the
double precision argument
x
(between –1 and 1) passed to it. Like
acos(), this effectively returns the inverse of the
sin() function. The result is a double precision
value of an angle, in radians, between
pi / 2
and
–pi / 2
.
Example
testdb=#
SELECT asin(1), asin(0), asin(-1),
testdb-#
asin(sin(1)) AS inverse_example;
asin | asin | asin | inverse_example
-----------------+------+------------------+-----------------
1.5707963267949 | 0 | -1.5707963267949 | 1
(1 row)
atan(
x
)
The atan() function returns the inverse (or arc) tangent of a
double precision argument
x
passed to it, which
effectively returns the inverse of the tan() function. The result is
a double precision value of an angle, in radians, between
pi / 2
and
–pi / 2
.
Example
testdb=#
SELECT atan(1), atan(0), atan(-1),
testdb-#
atan(tan(1)) AS inverse_example;
atan | atan | atan | inverse_example
-------------------+------+--------------------+-----------------
0.785398163397448 | 0 | -0.785398163397448 | 1
(1 row)
atan2(
x
,
y
)
Similar to the atan() function, the atan2()
returns the inverse (or arc) tangent in the form of a double precision value of an
angle, in radians, between
pi / 2
and
–pi / 2
. Unlike atan()
atan2() accepts two double precision arguments rather
than one, and returns the inverse tangent of the quotient of the first argument divided into the second argument.
In general, atan2(
x
,
y
) is functionally identical to
atan(
x
/
y
), though specifying a
y
value of 0
will not cause a divide by zero error with atan2, as it would if dividing
x
/
y
to the atan() function. If
y
is specified to atan2() as zero, the resultant value will be
pi / 2
for a positive value of
x
,
–pi / 2
for a
negative value of
x
, or 0 for a zero value of
x
.
Example
testdb=#
SELECT atan2(0, 1), atan2(1, 1),
testdb-#
atan(0 / 1) AS functionally,
testdb-#
atan(1 / 1) AS identical;
atan2 | atan2 | functionally | identical
-------+-------------------+--------------+-------------------
0 | 0.785398163397448 | 0 | 0.785398163397448
(1 row)
testdb=#
SELECT atan2(1, 0) AS positive_x,
testdb-#
atan2(-1, 0) AS negative_x,
testdb-#
atan2(0, 0) AS zero_x,
testdb-#
pi() / 2 AS pi_over_two;
positive_x | negative_x | zero_x | pi_over_two
-----------------+------------------+--------+-----------------
1.5707963267949 | -1.5707963267949 | 0 | 1.5707963267949
(1 row)
cbrt(
x
)
The cbrt() function accepts a single double precision
argument
x
, and returns its cubed root as a double precision value.
This function is effectively the inverse of raising a number by the power of 3 with the pow
function.
Example
testdb=#
SELECT pow(2.0, 3) AS "two cubed",
testdb-#
cbrt(8.0) AS "eight's cube root";
two cubed | eight's cube root
-----------+-------------------
8 | 2
(1 row)
ceil(
x
)
The ceil() function accepts a value
x
of any
numeric data type (numeric, bigint,
smallint, real, or
double precision), and rounds it up to the smallest whole
integer greater than the passed value. If a whole integer is passed, ceil()
has no effect.
Example
testdb=#
SELECT ceil(1.0), ceil(1.1), ceil(1.5);
ceil | ceil | ceil
------+------+------
1 | 2 | 2
(1 row)
cos(
x
)
The cos() function accepts a single double precision
value
x
representing an angle (in radians), and returns its cosine as a
double precision value.
Example
testdb=#
SELECT cos(pi()) AS cos_pi,
testdb-#
cos(0) AS cos_zero;
cos_pi | cos_zero
--------+----------
-1 | 1
(1 row)
cot(
x
)
The cot() function accepts a single double precision
value
x
representing an angle (in radians), and returns its cotangent as a
double precision value. The argument passed must be non-zero.
Example
testdb=#
SELECT cot(1), cot(-1);
cot | cot
-------------------+--------------------
0.642092615934331 | -0.642092615934331
(1 row)
degrees(
r
)
The degrees() function accepts a double precision
argument
r
representing a value expressed in radians, and converts them into degrees. The result is
returned as a value of type double precision. degrees()
is effectively the inverse function of the radians() function.
Example
testdb=#
SELECT degrees(acos(-1)) AS half_circle,
testdb-#
degrees(pi() * 2) AS full_circle;
half_circle | full_circle
-------------+-------------
180 | 360
(1 row)
exp(
x
)
The exp() function accepts a single double precision
or numeric argument
x
, and returns the special
e
constant, raised to the power passed to the function.
Example
testdb=#
SELECT exp(0.0) AS one,
testdb-#
exp(1.0) AS e,
testdb-#
exp(2.0) AS "e squared";
one | e | e squared
-----+------------------+------------------
1 | 2.71828182845905 | 7.38905609893065
(1 row)
floor(
x
)
The floor() function accepts a single numeric
value
x
, and rounds it down to the largest whole integer not greater than the passed argument. It
therefore has no effect on a whole integer.
Example
testdb=#
SELECT floor(1.0) AS one,
testdb-#
floor(1.1) AS "one point one",
testdb-#
floor(1.8) AS "one point eight";
one | one point one | one point eight
-----+---------------+-----------------
1 | 1 | 1
(1 row)
ln(
x
)
ln() accepts a single numeric or
double precision value
x
and returns the natural logarithm of
that argument. This is effectively the inverse of the exp() function, as well as the
equivalent of selecting the log() of the argument, with base
e
.
Example
testdb=#
SELECT ln(10.0) AS natural_log,
testdb-#
log(exp(1.0), 10.0) AS natural_log,
testdb-#
ln(exp(10.0)) AS inverse_example;
natural_log | natural_log | inverse_example
------------------+------------------+-----------------
2.30258509299405 | 2.30258509299404 | 10
(1 row)
log(
x
)
log(
b
,
x
)
The log() function accepts either one or two arguments of
type numeric. If one argument is specified, log(
x
)
returns the base 10 logarithm of the
x
. If two arguments are specified,
log(
b
,
x
) returns the base
b
logarithm of
x
.
Example
testdb=#
SELECT log(12.0) AS log_12,
testdb-#
log(10, 12.0) AS log_12,
testdb-#
log(3, 12.0) AS "log 12, base 3";
log_12 | log_12 | log 12, base 3
------------------+--------------+----------------
1.07918124604762 | 1.0791812460 | 2.2618595071
(1 row)
mod(
x
,
y
)
The mod function accepts two numeric arguments,
x
and
y
, which may be of type numeric,
integer, smallint, or
bigint. The value returned is the remainder, or modulus, left over from dividing
x
/
y
, and is of the same data type which is passed to the function.
Example
testdb=#
SELECT mod(5, 5) AS no_remainder,
testdb-#
mod(6, 5) AS remainder_one,
testdb-#
mod(19, 5) AS remainder_four;
no_remainder | remainder_one | remainder_four
--------------+---------------+----------------
0 | 1 | 4
(1 row)
pi()
The pi() function requires no arguments, and returns the
pi
constant of roughly 3.14159265358979.
Example
testdb=#
SELECT pi() AS "the pi constant";
the pi constant
------------------
3.14159265358979
(1 row)
pow(
x
,
y
)
The pow() function accepts two arguments,
x
and
y
, of type numeric or double precision.
It returns the value of
x
raised to the exponent of
y
. The result is
returned as a value of the same data type as the passed arguments. Note that the arguments must contain decimal points.
Example
testdb=#
SELECT pow(2.0, 3.0) AS "two cubed",
testdb-#
pow(2.0, 2.0) AS "two squared",
testdb-#
pow(2.0, 1.0) AS "just two";
two cubed | two squared | just two
-----------+-------------+----------
8 | 4 | 2
(1 row)
radians(
d
)
The radians() function accepts a single argument
d
of type
double precision, specifying degrees. The function returns the equivalent number of
radians, as a value of type double precision.
radians() is effectively the inverse of the
degrees() function.
Example
testdb=#
SELECT radians(180) AS half_circle,
testdb-#
radians(360) AS full_circle;
half_circle | full_circle
------------------+------------------
3.14159265358979 | 6.28318530717959
(1 row)
random()
The random() function accepts no arguments, and returns
a pseudo-random value between 0.0 and 1.0, of type double precision.
Each invocation of random() returns a different value, even when used in
multiple places within the same query.
Typically this function is used in conjunction with mathematical operators (e.g., +
and *) to set a range of random numbers, and then rounded with an appropriate
rounding function (e.g., round(), trunc()).
Example
testdb=#
SELECT random() AS natural_random,
testdb-#
round(random() * 9) + 1 AS one_through_ten,
testdb-#
trunc(random() * 99) + 1 AS one_through_one_hundred;
natural_random | one_through_ten | one_through_one_hundred
-------------------+-----------------+-------------------------
0.478887704424042 | 2 | 37
(1 row)
round(
x
)
round(
x
,
s
)
The round() function may accept either one or two arguments. The first argument,
x
, of type numeric or double precision,
is the number that you intend to round. The second optional argument,
s
, of type
integer, specifies how many digits past the decimal to round from. The result is returned
as a value of the same type as the first argument.
If there are more digits specified by
s
than by
x
,
the extra digits will be padded with zeroes.
Example
testdb=#
SELECT round(1.0) AS one,
testdb-#
round(1.1) AS "one point one",
testdb-#
round(1.5) AS "one point five",
testdb-#
round(1.8) AS "one point eight";
one | one point one | one point five | one point eight
-----+---------------+----------------+-----------------
1 | 1 | 2 | 2
(1 row)
testdb=#
SELECT round(1.4949, 1) AS one_digit_scale,
testdb-#
round(1.4949, 3) AS three_digit_scale,
testdb-#
round(1.4949, 10) AS ten_digit_scale,
testdb-#
round(1.4949, 0) AS rounded;
one_digit_scale | three_digit_scale | ten_digit_scale | rounded
-----------------+-------------------+-----------------+---------
1.5 | 1.495 | 1.4949000000 | 1
(1 row)
sin(
x
)
The sin() function accepts a single argument
x
of type
double precision, representing an angle described in radians. The sine of
the argument is returned as a value of type double precision.
Example
testdb=#
SELECT sin(pi() / 4) AS quarter_pi,
testdb-#
sin(pi() / 2) AS half_pi;
quarter_pi | half_pi
-------------------+---------
0.707106781186547 | 1
(1 row)
sqrt(
x
)
The sqrt() function accepts a single argument
x
, of either type
double precision, or numeric, and returns its square
root. The returned value is of the same data type passed to it. The sqrt function is
effectively the inverse of the pow() function, used with a power of
2.
Example
testdb=#
SELECT sqrt(2.0), sqrt(4.0),
testdb-#
sqrt(pow(2.0, 2)) AS inverse_example;
sqrt | sqrt | inverse_example
-----------------+------+-----------------
1.4142135623731 | 2 | 2
(1 row)
tan(
x
)
The tan() function accepts a single argument
x
, of type
double precision, representing an angle described in radians. The
tangent of the argument is returned as a value of type double precision.
Example
testdb=#
SELECT tan(pi() / 8),
testdb-#
tan(0);
tan | tan
-------------------+-----
0.414213562373095 | 0
(1 row)
trunc(
x
)
trunc(
x
,
s
)
The trunc() function accepts one or two arguments,
x
and
s
. The
x
argument may be of the numeric or
double precision type, and represents the value to be truncated. The
s
argument may be of the integer type.
If specified,
s
dictates the number of digits allowed to the right of the decimal before
truncation. If unspecified, any digits past the decimal in
x
are truncated. If more digits are
specified by
s
than there are represented by
x
, the extra digits will be padded
with zeroes.
Example
testdb=#
SELECT trunc(1.598) AS natural_truncation,
testdb-#
trunc(1.598, 1) AS one_decimal_point,
testdb-#
trunc(1.598, 8) AS extra_places;
natural_truncation | one_decimal_point | extra_places
--------------------+-------------------+--------------
1 | 1.5 | 1.59800000
(1 row)
