## 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](functions-math.html#FUNCTIONS-MATH-OP-TABLE) 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<br/><br/> Description<br/><br/> Example(s)                                                                                                                                            |
|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|                                                                                                                   *`numeric_type`* `+` *`numeric_type`* → `*`numeric_type`*`<br/><br/> Addition<br/><br/>`2 + 3` → `5`                                                                                                                    |
|                                                                                                                  `+` *`numeric_type`* → `*`numeric_type`*`<br/><br/> Unary plus (no operation)<br/><br/>`+ 3.5` → `3.5`                                                                                                                   |
|                                                                                                                 *`numeric_type`* `-` *`numeric_type`* → `*`numeric_type`*`<br/><br/> Subtraction<br/><br/>`2 - 3` → `-1`                                                                                                                  |
|                                                                                                                           `-` *`numeric_type`* → `*`numeric_type`*`<br/><br/> Negation<br/><br/>`- (-4)` → `4`                                                                                                                            |
|                                                                                                                *`numeric_type`* `*` *`numeric_type`* → `*`numeric_type`*`<br/><br/> Multiplication<br/><br/>`2 * 3` → `6`                                                                                                                 |
|                                                *`numeric_type`* `/` *`numeric_type`* → `*`numeric_type`*`<br/><br/> Division (for integral types, division truncates the result towards zero)<br/><br/>`5.0 / 2` → `2.5000000000000000`<br/><br/>`5 / 2` → `2`<br/><br/>`(-5) / 2` → `-2`                                                 |
|                                                                               *`numeric_type`* `%` *`numeric_type`* → `*`numeric_type`*`<br/><br/> Modulo (remainder); available for `smallint`, `integer`, `bigint`, and `numeric`<br/><br/>`5 % 4` → `1`                                                                                |
|`numeric` `^` `numeric` → `numeric`<br/><br/>`double precision` `^` `double precision` → `double precision`<br/><br/> Exponentiation<br/><br/>`2 ^ 3` → `8`<br/><br/> Unlike typical mathematical practice, multiple uses of `^` will associate left to right by default:<br/><br/>`2 ^ 3 ^ 3` → `512`<br/><br/>`2 ^ (3 ^ 3)` → `134217728`|
|                                                                                                                        `|/` `double precision` → `double precision`<br/><br/> Square root<br/><br/>`|/ 25.0` → `5`                                                                                                                        |
|                                                                                                                        `||/` `double precision` → `double precision`<br/><br/> Cube root<br/><br/>`||/ 64.0` → `4`                                                                                                                        |
|                                                                                                                        `@` *`numeric_type`* → `*`numeric_type`*`<br/><br/> Absolute value<br/><br/>`@ -5.0` → `5`                                                                                                                         |
|                                                                                                               *`integral_type`* `&` *`integral_type`* → `*`integral_type`*`<br/><br/> Bitwise AND<br/><br/>`91 & 15` → `11`                                                                                                               |
|                                                                                                                *`integral_type`* `|` *`integral_type`* → `*`integral_type`*`<br/><br/> Bitwise OR<br/><br/>`32 | 3` → `35`                                                                                                                |
|                                                                                                           *`integral_type`* `#` *`integral_type`* → `*`integral_type`*`<br/><br/> Bitwise exclusive OR<br/><br/>`17 # 5` → `20`                                                                                                           |
|                                                                                                                          `~` *`integral_type`* → `*`integral_type`*`<br/><br/> Bitwise NOT<br/><br/>`~1` → `-2`                                                                                                                           |
|                                                                                                               *`integral_type`* `<<` `integer` → `*`integral_type`*`<br/><br/> Bitwise shift left<br/><br/>`1 << 4` → `16`                                                                                                                |
|                                                                                                               *`integral_type`* `>>` `integer` → `*`integral_type`*`<br/><br/> Bitwise shift right<br/><br/>`8 >> 2` → `2`                                                                                                                |

[Table 9.5](functions-math.html#FUNCTIONS-MATH-FUNC-TABLE) 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<br/><br/> Description<br/><br/> Example(s)                                                                                                                                                                                                                                                                |
|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|                                                                                                                                                                                                                                   []() `abs` ( *`numeric_type`* ) → `*`numeric_type`*`<br/><br/> Absolute value<br/><br/>`abs(-17.4)` → `17.4`                                                                                                                                                                                                                                    |
|                                                                                                                                                                                                                                      []() `cbrt` ( `double precision` ) → `double precision`<br/><br/> Cube root<br/><br/>`cbrt(64.0)` → `4`                                                                                                                                                                                                                                      |
|                                                                                                                                                                             []() `ceil` ( `numeric` ) → `numeric`<br/><br/>`ceil` ( `double precision` ) → `double precision`<br/><br/> Nearest integer greater than or equal to argument<br/><br/>`ceil(42.2)` → `43`<br/><br/>`ceil(-42.8)` → `-42`                                                                                                                                                                             |
|                                                                                                                                                                               []() `ceiling` ( `numeric` ) → `numeric`<br/><br/>`ceiling` ( `double precision` ) → `double precision`<br/><br/> Nearest integer greater than or equal to argument (same as `ceil`)<br/><br/>`ceiling(95.3)` → `96`                                                                                                                                                                                |
|                                                                                                                                                                                                                  []() `degrees` ( `double precision` ) → `double precision`<br/><br/> Converts radians to degrees<br/><br/>`degrees(0.5)` → `28.64788975654116`                                                                                                                                                                                                                   |
|                                                                                                                                                                                                             []() `div` ( *`y`* `numeric`, *`x`* `numeric` ) → `numeric`<br/><br/> Integer quotient of *`y`*/*`x`* (truncates towards zero)<br/><br/>`div(9, 4)` → `2`                                                                                                                                                                                                             |
|                                                                                                                                                                                         []() `exp` ( `numeric` ) → `numeric`<br/><br/>`exp` ( `double precision` ) → `double precision`<br/><br/> Exponential (`e` raised to the given power)<br/><br/>`exp(1.0)` → `2.7182818284590452`                                                                                                                                                                                          |
|                                                                                                                                                                                                                                           []() `factorial` ( `bigint` ) → `numeric`<br/><br/> Factorial<br/><br/>`factorial(5)` → `120`                                                                                                                                                                                                                                           |
|                                                                                                                                                                            []() `floor` ( `numeric` ) → `numeric`<br/><br/>`floor` ( `double precision` ) → `double precision`<br/><br/> Nearest integer less than or equal to argument<br/><br/>`floor(42.8)` → `42`<br/><br/>`floor(-42.8)` → `-43`                                                                                                                                                                             |
|                                                                                                                                     []() `gcd` ( *`numeric_type`*, *`numeric_type`* ) → `*`numeric_type`*`<br/><br/> Greatest common divisor (the largest positive number that divides both inputs with no remainder); returns `0` if both inputs are zero; available for `integer`, `bigint`, and `numeric`<br/><br/>`gcd(1071, 462)` → `21`                                                                                                                                     |
|                                                                                                                               []() `lcm` ( *`numeric_type`*, *`numeric_type`* ) → `*`numeric_type`*`<br/><br/> Least common multiple (the smallest strictly positive number that is an integral multiple of both inputs); returns `0` if either input is zero; available for `integer`, `bigint`, and `numeric`<br/><br/>`lcm(1071, 462)` → `23562`                                                                                                                               |
|                                                                                                                                                                                                        []() `ln` ( `numeric` ) → `numeric`<br/><br/>`ln` ( `double precision` ) → `double precision`<br/><br/> Natural logarithm<br/><br/>`ln(2.0)` → `0.6931471805599453`                                                                                                                                                                                                        |
|                                                                                                                                                                                                               []() `log` ( `numeric` ) → `numeric`<br/><br/>`log` ( `double precision` ) → `double precision`<br/><br/> Base 10 logarithm<br/><br/>`log(100)` → `2`                                                                                                                                                                                                               |
|                                                                                                                                                                                                   []() `log10` ( `numeric` ) → `numeric`<br/><br/>`log10` ( `double precision` ) → `double precision`<br/><br/> Base 10 logarithm (same as `log`)<br/><br/>`log10(1000)` → `3`                                                                                                                                                                                                    |
|                                                                                                                                                                                                                   `log` ( *`b`* `numeric`, *`x`* `numeric` ) → `numeric`<br/><br/> Logarithm of *`x`* to base *`b`*<br/><br/>`log(2.0, 64.0)` → `6.0000000000`                                                                                                                                                                                                                    |
|                                                                                                                                                                                           []() `min_scale` ( `numeric` ) → `integer`<br/><br/> Minimum scale (number of fractional decimal digits) needed to represent the supplied value precisely<br/><br/>`min_scale(8.4100)` → `2`                                                                                                                                                                                            |
|                                                                                                                                                                                  []() `mod` ( *`y`* *`numeric_type`*, *`x`* *`numeric_type`* ) → `*`numeric_type`*`<br/><br/> Remainder of *`y`*/*`x`*; available for `smallint`, `integer`, `bigint`, and `numeric`<br/><br/>`mod(9, 4)` → `1`                                                                                                                                                                                   |
|                                                                                                                                                                                                                                     []() `pi` ( ) → `double precision`<br/><br/> Approximate value of π<br/><br/>`pi()` → `3.141592653589793`                                                                                                                                                                                                                                     |
|                                                                                                                                                                       []() `power` ( *`a`* `numeric`, *`b`* `numeric` ) → `numeric`<br/><br/>`power` ( *`a`* `double precision`, *`b`* `double precision` ) → `double precision`<br/><br/>*`a`* raised to the power of *`b`*<br/><br/>`power(9, 3)` → `729`                                                                                                                                                                       |
|                                                                                                                                                                                                                 []() `radians` ( `double precision` ) → `double precision`<br/><br/> Converts degrees to radians<br/><br/>`radians(45.0)` → `0.7853981633974483`                                                                                                                                                                                                                  |
|                                                                                                          []() `round` ( `numeric` ) → `numeric`<br/><br/>`round` ( `double precision` ) → `double precision`<br/><br/> Rounds to nearest integer. For `numeric`, ties are broken by rounding away from zero. For `double precision`, the tie-breaking behavior is platform dependent, but “round to nearest even” is the most common rule.<br/><br/>`round(42.4)` → `42`                                                                                                          |
|                                                                                                                                                                                            `round` ( *`v`* `numeric`, *`s`* `integer` ) → `numeric`<br/><br/> Rounds *`v`* to *`s`* decimal places. Ties are broken by rounding away from zero.<br/><br/>`round(42.4382, 2)` → `42.44`                                                                                                                                                                                            |
|                                                                                                                                                                                                            []() `scale` ( `numeric` ) → `integer`<br/><br/> Scale of the argument (the number of decimal digits in the fractional part)<br/><br/>`scale(8.4100)` → `4`                                                                                                                                                                                                            |
|                                                                                                                                                                                                   []() `sign` ( `numeric` ) → `numeric`<br/><br/>`sign` ( `double precision` ) → `double precision`<br/><br/> Sign of the argument (-1, 0, or +1)<br/><br/>`sign(-8.4)` → `-1`                                                                                                                                                                                                    |
|                                                                                                                                                                                                         []() `sqrt` ( `numeric` ) → `numeric`<br/><br/>`sqrt` ( `double precision` ) → `double precision`<br/><br/> Square root<br/><br/>`sqrt(2)` → `1.4142135623730951`                                                                                                                                                                                                         |
|                                                                                                                                                                                             []() `trim_scale` ( `numeric` ) → `numeric`<br/><br/> Reduces the value's scale (number of fractional decimal digits) by removing trailing zeroes<br/><br/>`trim_scale(8.4100)` → `8.41`                                                                                                                                                                                              |
|                                                                                                                                                                                  []() `trunc` ( `numeric` ) → `numeric`<br/><br/>`trunc` ( `double precision` ) → `double precision`<br/><br/> Truncates to integer (towards zero)<br/><br/>`trunc(42.8)` → `42`<br/><br/>`trunc(-42.8)` → `-42`                                                                                                                                                                                  |
|                                                                                                                                                                                                                 `trunc` ( *`v`* `numeric`, *`s`* `integer` ) → `numeric`<br/><br/> Truncates *`v`* to *`s`* decimal places<br/><br/>`trunc(42.4382, 2)` → `42.43`                                                                                                                                                                                                                 |
|          []() `width_bucket` ( *`operand`* `numeric`, *`low`* `numeric`, *`high`* `numeric`, *`count`* `integer` ) → `integer`<br/><br/>`width_bucket` ( *`operand`* `double precision`, *`low`* `double precision`, *`high`* `double precision`, *`count`* `integer` ) → `integer`<br/><br/> Returns the number of the bucket in which *`operand`* falls in a histogram having *`count`* equal-width buckets spanning the range *`low`* to *`high`*. Returns `0` or `*`count`*+1` for an input outside that range.<br/><br/>`width_bucket(5.35, 0.024, 10.06, 5)` → `3`          |
|`width_bucket` ( *`operand`* `anycompatible`, *`thresholds`* `anycompatiblearray` ) → `integer`<br/><br/> Returns the number of the bucket in which *`operand`* falls given an array listing the lower bounds of the buckets. Returns `0` for an input less than the first lower bound. *`operand`* and the array elements can be of any type having standard comparison operators. The *`thresholds`* array *must be sorted*, smallest first, or unexpected results will be obtained.<br/><br/>`width_bucket(now(), array['yesterday', 'today', 'tomorrow']::timestamptz[])` → `2`|

[Table 9.6](functions-math.html#FUNCTIONS-MATH-RANDOM-TABLE) shows functions for generating random numbers.

**Table 9.6. Random Functions**

|                                                                 Function<br/><br/> Description<br/><br/> Example(s)                                                                |
|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|                  []() `random` ( ) → `double precision`<br/><br/> Returns a random value in the range 0.0 \<= x \< 1.0<br/><br/>`random()` → `0.897124072839091`                   |
|[]() `setseed` ( `double precision` ) → `void`<br/><br/> Sets the seed for subsequent `random()` calls; argument must be between -1.0 and 1.0, inclusive<br/><br/>`setseed(0.12345)`|

 The `random()` function uses a simple linear congruential algorithm. It is fast but not suitable for cryptographic applications; see the [pgcrypto](pgcrypto.html) 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.

[Table 9.7](functions-math.html#FUNCTIONS-MATH-TRIG-TABLE) 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<br/><br/> Description<br/><br/> Example(s)                                                                       |
|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
|                                   []() `acos` ( `double precision` ) → `double precision`<br/><br/> Inverse cosine, result in radians<br/><br/>`acos(1)` → `0`                                   |
|                                []() `acosd` ( `double precision` ) → `double precision`<br/><br/> Inverse cosine, result in degrees<br/><br/>`acosd(0.5)` → `60`                                 |
|                           []() `asin` ( `double precision` ) → `double precision`<br/><br/> Inverse sine, result in radians<br/><br/>`asin(1)` → `1.5707963267948966`                            |
|                                 []() `asind` ( `double precision` ) → `double precision`<br/><br/> Inverse sine, result in degrees<br/><br/>`asind(0.5)` → `30`                                  |
|                          []() `atan` ( `double precision` ) → `double precision`<br/><br/> Inverse tangent, result in radians<br/><br/>`atan(1)` → `0.7853981633974483`                          |
|                                 []() `atand` ( `double precision` ) → `double precision`<br/><br/> Inverse tangent, result in degrees<br/><br/>`atand(1)` → `45`                                 |
|[]() `atan2` ( *`y`* `double precision`, *`x`* `double precision` ) → `double precision`<br/><br/> Inverse tangent of *`y`*/*`x`*, result in radians<br/><br/>`atan2(1, 0)` → `1.5707963267948966`|
|       []() `atan2d` ( *`y`* `double precision`, *`x`* `double precision` ) → `double precision`<br/><br/> Inverse tangent of *`y`*/*`x`*, result in degrees<br/><br/>`atan2d(1, 0)` → `90`       |
|                                       []() `cos` ( `double precision` ) → `double precision`<br/><br/> Cosine, argument in radians<br/><br/>`cos(0)` → `1`                                       |
|                                    []() `cosd` ( `double precision` ) → `double precision`<br/><br/> Cosine, argument in degrees<br/><br/>`cosd(60)` → `0.5`                                     |
|                            []() `cot` ( `double precision` ) → `double precision`<br/><br/> Cotangent, argument in radians<br/><br/>`cot(0.5)` → `1.830487721712452`                             |
|                                    []() `cotd` ( `double precision` ) → `double precision`<br/><br/> Cotangent, argument in degrees<br/><br/>`cotd(45)` → `1`                                    |
|                               []() `sin` ( `double precision` ) → `double precision`<br/><br/> Sine, argument in radians<br/><br/>`sin(1)` → `0.8414709848078965`                                |
|                                     []() `sind` ( `double precision` ) → `double precision`<br/><br/> Sine, argument in degrees<br/><br/>`sind(30)` → `0.5`                                      |
|                              []() `tan` ( `double precision` ) → `double precision`<br/><br/> Tangent, argument in radians<br/><br/>`tan(1)` → `1.5574077246549023`                              |
|                                     []() `tand` ( `double precision` ) → `double precision`<br/><br/> Tangent, argument in degrees<br/><br/>`tand(45)` → `1`                                     |

### 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](functions-math.html#FUNCTIONS-MATH-HYP-TABLE) shows the available hyperbolic functions.

**Table 9.8. Hyperbolic Functions**

|                                            Function<br/><br/> Description<br/><br/> Example(s)                                           |
|------------------------------------------------------------------------------------------------------------------------------------------|
|       []() `sinh` ( `double precision` ) → `double precision`<br/><br/> Hyperbolic sine<br/><br/>`sinh(1)` → `1.1752011936438014`        |
|               []() `cosh` ( `double precision` ) → `double precision`<br/><br/> Hyperbolic cosine<br/><br/>`cosh(0)` → `1`               |
|      []() `tanh` ( `double precision` ) → `double precision`<br/><br/> Hyperbolic tangent<br/><br/>`tanh(1)` → `0.7615941559557649`      |
|   []() `asinh` ( `double precision` ) → `double precision`<br/><br/> Inverse hyperbolic sine<br/><br/>`asinh(1)` → `0.881373587019543`   |
|          []() `acosh` ( `double precision` ) → `double precision`<br/><br/> Inverse hyperbolic cosine<br/><br/>`acosh(1)` → `0`          |
|[]() `atanh` ( `double precision` ) → `double precision`<br/><br/> Inverse hyperbolic tangent<br/><br/>`atanh(0.5)` → `0.5493061443340548`|