## 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 available mathematical operators.

Table 9.4. Mathematical Operators

OperatorDescriptionExampleResult
`+`addition`2 + 3``5`
`-`subtraction`2 - 3``-1`
`*`multiplication`2 * 3``6`
`/`division (integer division truncates the result)`4 / 2``2`
`%`modulo (remainder)`5 % 4``1`
`^`exponentiation (associates left to right)`2.0 ^ 3.0``8`
`|/`square root`|/ 25.0``5`
`||/`cube root`||/ 27.0``3`
`!`factorial`5 !``120`
`!!`factorial (prefix operator)`!! 5``120`
`@`absolute value`@ -5.0``5`
`&`bitwise AND`91 & 15``11`
`|`bitwise OR`32 | 3``35`
`#`bitwise XOR`17 # 5``20`
`~`bitwise NOT`~1``-2`
`<<`bitwise shift left`1 << 4``16`
`>>`bitwise shift right`8 >> 2``2`

The bitwise operators work only on integral data types, whereas the others are available for all numeric data types. The bitwise operators are also available for the bit string types `bit` and `bit varying`, as shown in Table 9.13.

Table 9.5 shows the available mathematical functions. In the table, `dp` indicates `double precision`. 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. 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

FunctionReturn TypeDescriptionExampleResult
`abs(x)`(same as input)absolute value`abs(-17.4)``17.4`
`cbrt(dp)``dp`cube root`cbrt(27.0)``3`
`ceil(dp or numeric)`(same as input)nearest integer greater than or equal to argument`ceil(-42.8)``-42`
`ceiling(dp or numeric)`(same as input)nearest integer greater than or equal to argument (same as `ceil`)`ceiling(-95.3)``-95`
`degrees(dp)``dp`radians to degrees`degrees(0.5)``28.6478897565412`
`div(y numeric, x numeric)``numeric`integer quotient of `y`/`x``div(9,4)``2`
`exp(dp or numeric)`(same as input)exponential`exp(1.0)``2.71828182845905`
`floor(dp or numeric)`(same as input)nearest integer less than or equal to argument`floor(-42.8)``-43`
`ln(dp or numeric)`(same as input)natural logarithm`ln(2.0)``0.693147180559945`
`log(dp or numeric)`(same as input)base 10 logarithm`log(100.0)``2`
`log(b numeric, x numeric)``numeric`logarithm to base `b``log(2.0, 64.0)``6.0000000000`
`mod(y, x)`(same as argument types)remainder of `y`/`x``mod(9,4)``1`
`pi()``dp`π constant`pi()``3.14159265358979`
`power(a dp, b dp)``dp``a` raised to the power of `b``power(9.0, 3.0)``729`
`power(a numeric, b numeric)``numeric``a` raised to the power of `b``power(9.0, 3.0)``729`
`radians(dp)``dp`degrees to radians`radians(45.0)``0.785398163397448`
`round(dp or numeric)`(same as input)round to nearest integer`round(42.4)``42`
`round(v numeric, s int)``numeric`round to `s` decimal places`round(42.4382, 2)``42.44`
`scale(numeric)``integer`scale of the argument (the number of decimal digits in the fractional part)`scale(8.41)``2`
`sign(dp or numeric)`(same as input)sign of the argument (-1, 0, +1)`sign(-8.4)``-1`
`sqrt(dp or numeric)`(same as input)square root`sqrt(2.0)``1.4142135623731`
`trunc(dp or numeric)`(same as input)truncate toward zero`trunc(42.8)``42`
`trunc(v numeric, s int)``numeric`truncate to `s` decimal places`trunc(42.4382, 2)``42.43`
`width_bucket(operand dp, b1 dp, b2 dp, count int)``int`return the bucket number to which `operand` would be assigned in a histogram having `count` equal-width buckets spanning the range `b1` to `b2`; returns `0` or `count+1` for an input outside the range`width_bucket(5.35, 0.024, 10.06, 5)``3`
`width_bucket(operand numeric, b1 numeric, b2 numeric, count int)``int`return the bucket number to which `operand` would be assigned in a histogram having `count` equal-width buckets spanning the range `b1` to `b2`; returns `0` or `count+1` for an input outside the range`width_bucket(5.35, 0.024, 10.06, 5)``3`
`width_bucket(operand anyelement, thresholds anyarray)``int`return the bucket number to which `operand` would be assigned given an array listing the lower bounds of the buckets; returns `0` for an input less than the first lower bound; the `thresholds` array must be sorted, smallest first, or unexpected results will be obtained`width_bucket(now(), array['yesterday', 'today', 'tomorrow']::timestamptz[])``2`

Table 9.6 shows functions for generating random numbers.

Table 9.6. Random Functions

FunctionReturn TypeDescription
`random()``dp`random value in the range 0.0 <= x < 1.0
`setseed(dp)``void`set seed for subsequent `random()` calls (value between -1.0 and 1.0, inclusive)

The characteristics of the values returned by `random()` depend on the system implementation. It is not suitable for cryptographic applications; see pgcrypto module for an alternative.

Finally, Table 9.7 shows the available trigonometric functions. All trigonometric functions take arguments and return values of type `double precision`. Each of the trigonometric functions comes in two variants, one that measures angles in radians and one that measures angles in degrees.

Table 9.7. Trigonometric Functions

`acos(x)``acosd(x)`inverse cosine
`asin(x)` `asind(x)`inverse sine
`atan(x)` `atand(x)`inverse tangent
`atan2(y, x)` `atan2d(y, x)`inverse tangent of `y/x`
`cos(x)` `cosd(x)`cosine
`cot(x)` `cotd(x)`cotangent
`sin(x)` `sind(x)`sine
`tan(x)` `tand(x)`tangent
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)`.