Mathematical operators are provided for many PostgreSQL types. For types without common mathematical conventions for all possible permutations (such as date/time types), we describe the actual behavior in subsequent sections.
Table 3-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.0 |
| |/ | Square root | |/ 25.0 | 5.0 |
| ||/ | Cubic root | ||/ 27.0 | 3 |
| ! | Factorial | 5 ! | 120 |
| !! | Factorial (prefix operator) | !! 5 | 120 |
| @ | Absolute value | @ -5.0 | 5.0 |
| & | 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 3-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 |
Table 3-4. Mathematical Functions
| Function | Return Type | Description | Example | Result |
|---|---|---|---|---|
| abs(x) | (same as argument type) | absolute value | abs(-17.4) | 17.4 |
| cbrt(double precision) | double precision | cube root | cbrt(27.0) | 3.0 |
| ceil(numeric) | numeric | smallest integer not less than argument | ceil(-42.8) | -42 |
| degrees(double precision) | double precision | convert radians to degrees | degrees(0.5) | 28.6478897565412 |
| exp(double precision) | double precision | exponential function | 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:
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.
Table 3-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.