## On this page

# 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**

Operator | Description | Example | Result |
---|---|---|---|

`+` |
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**

Function | Return Type | Description | Example | Result |
---|---|---|---|---|

`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**

Function | Return Type | Description |
---|---|---|

`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**

Function (radians) | Function (degrees) | Description |
---|---|---|

`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 roundoff error for special cases such as`sind(30)`

.

© 1996–2019 The PostgreSQL Global Development Group

Licensed under the PostgreSQL License.

https://www.postgresql.org/docs/9.6/functions-math.html