18th October 2018: PostgreSQL 11 Released!
Supported Versions: Current (11) / 10 / 9.6 / 9.5 / 9.4 / 9.3
Development Versions: devel
Unsupported versions: 9.2 / 9.1 / 9.0 / 8.4 / 8.3 / 8.2 / 8.1 / 8.0 / 7.4 / 7.3 / 7.2 / 7.1

4.3. Mathematical Functions and Operators

Mathematical operators are provided for many PostgreSQL types. For types without common mathematical conventions for all possible permutations (e.g. date/time types) we describe the actual behavior in subsequent sections.

Table 4-2. Mathematical Operators

Name Description Example Result
+ Addition 2 + 3 5
- Subtraction 2 - 3 -1
* Multiplication 2 * 3 6
/ Division (integer division truncates results) 4 / 2 2
% Modulo (remainder) 5 % 4 1
^ Exponentiation 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
& Binary AND 91 & 15 11
| Binary OR 32 | 3 35
# Binary XOR 17 # 5 20
~ Binary NOT ~1 -2
<< Binary shift left 1 << 4 16
>> Binary shift right 8 >> 2 2

The "binary" operators are also available for the bit string types BIT and BIT VARYING.

Table 4-3. Bit String Binary Operators

Example Result
B'10001' & B'01101' 00001
B'10001' | B'01101' 11101
B'10001' # B'01101' 11110
~ B'10001' 01110
B'10001' << 3 01000
B'10001' >> 2 00100
Bit string arguments to &, |, and # must be of equal length. When bit shifting, the original length of the string is preserved, as shown here.

Table 4-4. Mathematical Functions

Function Return Type Description Example Result
`abs`(x) (same as x) absolute value abs(-17.4) 17.4
`cbrt`(dp) dp cube root cbrt(27.0) 3
`ceil`(numeric) numeric smallest integer not less than argument ceil(-42.8) -42
`degrees`(dp) dp radians to degrees degrees(0.5) 28.6478897565412
`exp`(dp) dp exponential exp(1.0) 2.71828182845905
`floor`(numeric) numeric largest integer not greater than argument floor(-42.8) -43
`ln`(dp) dp natural logarithm ln(2.0) 0.693147180559945
`log`(dp) dp 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 "Pi" constant pi() 3.14159265358979
`pow`(`e` dp, `n` dp) dp raise a number to exponent `e` pow(9.0, 3.0) 729
`radians`(dp) dp degrees to radians radians(45.0) 0.785398163397448
`random`() dp value between 0.0 to 1.0 random()
`round`(dp) dp round to nearest integer round(42.4) 42
`round`(`v` numeric, `s` integer) numeric round to `s` decimal places round(42.4382, 2) 42.44
`sign`(numeric) numeric sign of the argument (-1, 0, +1) sign(-8.4) -1
`sqrt`(dp) dp square root sqrt(2.0) 1.4142135623731
`trunc`(dp) dp truncate toward zero trunc(42.8) 42
`trunc`(numeric, `s` integer) numeric truncate to `s` decimal places trunc(42.4382, 2) 42.43

In the table above, dp indicates double precision. The functions `exp`, `ln`, `log`, `pow`, `round` (1 argument), `sqrt`, and `trunc` (1 argument) are also available for the type numeric in place of double precision. Functions returning a numeric result take numeric input arguments, unless otherwise specified. Many of these functions are implemented on top of the host system's C library; accuracy and behavior in boundary cases could therefore vary depending on the host system.

Table 4-5. Trigonometric Functions

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

All trigonometric functions have arguments and return values of type double precision.