13th February 2020: PostgreSQL 12.2, 11.7, 10.12, 9.6.17, 9.5.21, and 9.4.26 Released!

Development Versions:
devel

PostgreSQL 9.6.17 Documentation | |||
---|---|---|---|

Prev | Up | Chapter 9. Functions and Operators | Next |

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(` |
(same as input) | absolute value | abs(-17.4) |
17.4 |

`cbrt(` |
dp |
cube root | cbrt(27.0) |
3 |

`ceil(` |
(same as input) | nearest integer greater than or equal to argument | ceil(-42.8) |
-42 |

`ceiling(` |
(same as input) | nearest integer greater than or equal to argument (same as `ceil` ) |
ceiling(-95.3) |
-95 |

`degrees(` |
dp |
radians to degrees | degrees(0.5) |
28.6478897565412 |

`div(` |
numeric |
integer quotient of y/x |
div(9,4) |
2 |

`exp(` |
(same as input) | exponential | exp(1.0) |
2.71828182845905 |

`floor(` |
(same as input) | nearest integer less than or equal to argument | floor(-42.8) |
-43 |

`ln(` |
(same as input) | natural logarithm | ln(2.0) |
0.693147180559945 |

`log(` |
(same as input) | base 10 logarithm | log(100.0) |
2 |

`log(` |
numeric |
logarithm to base b |
log(2.0, 64.0) |
6.0000000000 |

`mod(` |
(same as argument types) | remainder of y/x |
mod(9,4) |
1 |

`pi()` |
dp |
"π" constant | pi() |
3.14159265358979 |

`power(` |
dp |
a raised to the power of b |
power(9.0, 3.0) |
729 |

`power(` |
numeric |
a raised to the power of b |
power(9.0, 3.0) |
729 |

`radians(` |
dp |
degrees to radians | radians(45.0) |
0.785398163397448 |

`round(` |
(same as input) | round to nearest integer | round(42.4) |
42 |

`round(` |
numeric |
round to s decimal places |
round(42.4382, 2) |
42.44 |

`scale(` |
integer |
scale of the argument (the number of decimal digits in the fractional part) | scale(8.41) |
2 |

`sign(` |
(same as input) | sign of the argument (-1, 0, +1) | sign(-8.4) |
-1 |

`sqrt(` |
(same as input) | square root | sqrt(2.0) |
1.4142135623731 |

`trunc(` |
(same as input) | truncate toward zero | trunc(42.8) |
42 |

`trunc(` |
numeric |
truncate to s decimal places |
trunc(42.4382, 2) |
42.43 |

`width_bucket(` |
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 for an input outside the rangecount+1 |
width_bucket(5.35, 0.024, 10.06, 5) |
3 |

`width_bucket(` |
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 for an input outside the rangecount+1 |
width_bucket(5.35, 0.024, 10.06, 5) |
3 |

`width_bucket(` |
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(` |
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(` |
`acosd(` |
inverse cosine |

`asin(` |
`asind(` |
inverse sine |

`atan(` |
`atand(` |
inverse tangent |

`atan2(` |
`atan2d(` |
inverse tangent of y/x |

`cos(` |
`cosd(` |
cosine |

`cot(` |
`cotd(` |
cotangent |

`sin(` |
`sind(` |
sine |

`tan(` |
`tand(` |
tangent |

Note:Another way to work with angles measured in degrees is to use the unit transformation functionsand`radians()`

shown earlier. However, using the degree-based trigonometric functions is preferred, as that way avoids roundoff error for special cases such as`degrees()`

sind(30).

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.