## 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 (deprecated, use `factorial()` instead)`5 !``120`
`!!` factorial as a prefix operator (deprecated, use `factorial()` instead)`!! 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 and are also available for the bit string types `bit` and `bit varying`, as shown in Table 9.14.

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`
`factorial(bigint)` `numeric`factorial`factorial(5)``120`
`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`
`log10(dp or numeric)` (same as input)base 10 logarithm`log10(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 `random()` function uses a simple linear congruential algorithm. It is fast but not suitable for cryptographic applications; see the pgcrypto module for a more secure alternative. If `setseed()` is called, the results of subsequent `random()` calls in the current session are repeatable by re-issuing `setseed()` with the same argument.

Table 9.7 shows the available trigonometric functions. All these 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

### 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. All these functions take arguments and return values of type ```double precision```.

Table 9.8. Hyperbolic Functions

FunctionDescriptionExampleResult
`sinh(x)` hyperbolic sine`sinh(0)``0`
`cosh(x)` hyperbolic cosine`cosh(0)``1`
`tanh(x)` hyperbolic tangent`tanh(0)``0`
`asinh(x)` inverse hyperbolic sine`asinh(0)``0`
`acosh(x)` inverse hyperbolic cosine`acosh(1)``0`
`atanh(x)` inverse hyperbolic tangent`atanh(0)``0`