PostgreSQL
9.3. Mathematical Functions and Operators
Mathematical operators are provided for many PostgreSQL types. For types without standard mathematical conventions (e.g., date/time types) we describe the actual behavior in subsequent sections.
Table 9.4 shows the mathematical operators that are available for the standard numeric types. Unless otherwise noted, operators shown as accepting `numeric_type are available for all the types `smallint,
integer
, bigint
, numeric
, real
, and double precision
. Operators shown as accepting `integral_type are available for the types `smallint,
integer
, and bigint
. Except where noted, each form of an operator returns the same data type as its argument(s). Calls involving multiple argument data types, such as integer
+
numeric
, are resolved by using the type appearing later in these lists.
Table 9.4. Mathematical Operators
Operator Description Example(s) |
---|
`numeric_type Addition
|
Unary plus (no operation)
|
`numeric_type Subtraction
|
Negation
|
`numeric_type Multiplication
|
`numeric_type Division (for integral types, division truncates the result towards zero)
|
`numeric_type Modulo (remainder); available for
|
Exponentiation
Unlike typical mathematical practice, multiple uses of
|
`+ |
/` `+double precision` → Square root `+ |
/ 25.0+` → |
`+ |
/` `+double precision` → Cube root `+ |
/ 64.0+` → |
Absolute value
|
`integral_type Bitwise AND
|
`integral_type` `+ |
` _`+integral_type Bitwise OR `+32 |
3+` → |
`integral_type Bitwise exclusive OR
|
Bitwise NOT
|
`integral_type Bitwise shift left
|
`integral_type Bitwise shift right
|
+
Table 9.5 shows the available mathematical functions. Many of these functions are provided in multiple forms with different argument types. Except where noted, any given form of a function returns the same data type as its argument(s); cross-type cases are resolved in the same way as explained above for operators. The functions working with double precision
data are mostly implemented on top of the host system’s C library; accuracy and behavior in boundary cases can therefore vary depending on the host system.
Table 9.5. Mathematical Functions
Function Description Example(s) |
---|
`+abs+` ( _`+numeric_type+`_ ) → _`+numeric_type+`_ Absolute value
|
`+cbrt+` ( `+double precision+` ) → `+double precision+` Cube root
|
`+ceil+` ( `+numeric+` ) → `+numeric+`
Nearest integer greater than or equal to argument
|
`+ceiling+` ( `+numeric+` ) → `+numeric+`
Nearest integer greater than or equal to argument (same as
|
`+degrees+` ( `+double precision+` ) → `+double precision+` Converts radians to degrees
|
`+div+` ( _`+y+`_ `+numeric+`, _`+x+`_ `+numeric+` ) → `+numeric+` Integer quotient of `y
|
`+exp+` ( `+numeric+` ) → `+numeric+`
Exponential (
|
[FUNCTION-FACTORIAL .indexterm]# Factorial
|
`+floor+` ( `+numeric+` ) → `+numeric+`
Nearest integer less than or equal to argument
|
`+gcd+` ( _`+numeric_type+`_, _`+numeric_type+`_ ) → _`+numeric_type+`_ Greatest common divisor (the largest positive number that divides both inputs with no remainder); returns
|
`+lcm+` ( _`+numeric_type+`_, _`+numeric_type+`_ ) → _`+numeric_type+`_ Least common multiple (the smallest strictly positive number that is an integral multiple of both inputs); returns
|
`+ln+` ( `+numeric+` ) → `+numeric+`
Natural logarithm
|
`+log+` ( `+numeric+` ) → `+numeric+`
Base 10 logarithm
|
`+log10+` ( `+numeric+` ) → `+numeric+`
Base 10 logarithm (same as
|
Logarithm of `x
|
`+min_scale+` ( `+numeric+` ) → `+integer+` Minimum scale (number of fractional decimal digits) needed to represent the supplied value precisely
|
`+mod+` ( _`+y+`_ _`+numeric_type+`_, _`+x+`_ _`+numeric_type+`_ ) → _`+numeric_type+`_ Remainder of `y
|
`+pi+` ( ) → `+double precision+` Approximate value of π
|
`+power+` ( _`+a+`_ `+numeric+`, _`+b+`_ `+numeric+` ) → `+numeric+`
`a
|
`+radians+` ( `+double precision+` ) → `+double precision+` Converts degrees to radians
|
`+round+` ( `+numeric+` ) → `+numeric+`
Rounds to nearest integer. For
|
Rounds `v
|
`+scale+` ( `+numeric+` ) → `+integer+` Scale of the argument (the number of decimal digits in the fractional part)
|
`+sign+` ( `+numeric+` ) → `+numeric+`
Sign of the argument (-1, 0, or +1)
|
`+sqrt+` ( `+numeric+` ) → `+numeric+`
Square root
|
`+trim_scale+` ( `+numeric+` ) → `+numeric+` Reduces the value’s scale (number of fractional decimal digits) by removing trailing zeroes
|
`+trunc+` ( `+numeric+` ) → `+numeric+`
Truncates to integer (towards zero)
|
Truncates `v
|
`+width_bucket+` ( _`+operand+`_ `+numeric+`, _`+low+`_ `+numeric+`, _`+high+`_ `+numeric+`, _`+count+`_ `+integer+` ) → `+integer+`
Returns the number of the bucket in which `operand
|
Returns the number of the bucket in which `operand
|
+
Table 9.6 shows functions for generating random numbers.
Table 9.6. Random Functions
Function Description Example(s) |
---|
`+random+` ( ) → `+double precision+` Returns a random value in the range 0.0 ⇐ x < 1.0
|
`+setseed+` ( `+double precision+` ) → `+void+` Sets the seed for subsequent
|
+
The random()
function uses a deterministic pseudo-random number generator. It is fast but not suitable for cryptographic applications; see the pgcrypto module for a more secure alternative. If setseed()
is called, the series of results of subsequent random()
calls in the current session can be repeated by re-issuing setseed()
with the same argument. Without any prior setseed()
call in the same session, the first random()
call obtains a seed from a platform-dependent source of random bits.
Table 9.7 shows the available trigonometric functions. Each of these functions comes in two variants, one that measures angles in radians and one that measures angles in degrees.
Table 9.7. Trigonometric Functions
Function Description Example(s) |
---|
`+acos+` ( `+double precision+` ) → `+double precision+` Inverse cosine, result in radians
|
`+acosd+` ( `+double precision+` ) → `+double precision+` Inverse cosine, result in degrees
|
`+asin+` ( `+double precision+` ) → `+double precision+` Inverse sine, result in radians
|
`+asind+` ( `+double precision+` ) → `+double precision+` Inverse sine, result in degrees
|
`+atan+` ( `+double precision+` ) → `+double precision+` Inverse tangent, result in radians
|
`+atand+` ( `+double precision+` ) → `+double precision+` Inverse tangent, result in degrees
|
`+atan2+` ( _`+y+`_ `+double precision+`, _`+x+`_ `+double precision+` ) → `+double precision+` Inverse tangent of `y
|
`+atan2d+` ( _`+y+`_ `+double precision+`, _`+x+`_ `+double precision+` ) → `+double precision+` Inverse tangent of `y
|
`+cos+` ( `+double precision+` ) → `+double precision+` Cosine, argument in radians
|
`+cosd+` ( `+double precision+` ) → `+double precision+` Cosine, argument in degrees
|
`+cot+` ( `+double precision+` ) → `+double precision+` Cotangent, argument in radians
|
`+cotd+` ( `+double precision+` ) → `+double precision+` Cotangent, argument in degrees
|
`+sin+` ( `+double precision+` ) → `+double precision+` Sine, argument in radians
|
`+sind+` ( `+double precision+` ) → `+double precision+` Sine, argument in degrees
|
`+tan+` ( `+double precision+` ) → `+double precision+` Tangent, argument in radians
|
`+tand+` ( `+double precision+` ) → `+double precision+` Tangent, argument in degrees
|
+
Note
Another way to work with angles measured in degrees is to use the unit transformation functions radians()
and degrees()
shown earlier. However, using the degree-based trigonometric functions is preferred, as that way avoids round-off error for special cases such as sind(30)
.
Table 9.8 shows the available hyperbolic functions.
Table 9.8. Hyperbolic Functions
Function Description Example(s) |
---|
`+sinh+` ( `+double precision+` ) → `+double precision+` Hyperbolic sine
|
`+cosh+` ( `+double precision+` ) → `+double precision+` Hyperbolic cosine
|
`+tanh+` ( `+double precision+` ) → `+double precision+` Hyperbolic tangent
|
`+asinh+` ( `+double precision+` ) → `+double precision+` Inverse hyperbolic sine
|
`+acosh+` ( `+double precision+` ) → `+double precision+` Inverse hyperbolic cosine
|
`+atanh+` ( `+double precision+` ) → `+double precision+` Inverse hyperbolic tangent
|
+
Prev | Up | Next |
---|---|---|
9.2. Comparison Functions and Operators |
9.4. String Functions and Operators |
Submit correction
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.
Copyright © 1996-2023 The PostgreSQL Global Development Group