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

Table 9-2. 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-11.

Table 9-3 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-3. 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`
`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-4 shows functions for generating random numbers.

Table 9-4. 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-5 shows the available trigonometric functions. All trigonometric functions take arguments and return values of type `double precision`. Trigonometric functions arguments are expressed in radians. Inverse functions return values are expressed in radians. See unit transformation functions `radians()` and `degrees()` above.

Table 9-5. Trigonometric Functions

Function Description
`acos(x)` inverse cosine
`asin(x)` inverse sine
`atan(x)` inverse tangent
```atan2(y, x)``` inverse tangent of `y/x`
`cos(x)` cosine
`cot(x)` cotangent
`sin(x)` sine
`tan(x)` tangent

© 1996–2017 The PostgreSQL Global Development Group
https://www.postgresql.org/docs/9.5/static/functions-math.html

App下载  