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 6-2 shows the available mathematical operators.
Table 6-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, as
shown in Table 6-3.
Bit string arguments to &, |,
and # must be of equal length. When bit
shifting, the original length of the string is preserved, as shown in the table.
Table 6-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 6-4 shows the available
mathematical functions. In the table, 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 6-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,
r integer) | numeric | truncate to s decimal places | trunc(42.4382, 2) | 42.43 |
Finally, Table 6-5 shows the
available trigonometric functions. All trigonometric functions
have arguments and return values of type double
precision.
Table 6-5. Trigonometric Functions
| Function | Description |
|---|
| acos(x) | inverse cosine |
| asin(x) | inverse sine |
| atan(x) | inverse tangent |
| atan2(x,
y) | inverse tangent of
x/y |
| cos(x) | cosine |
| cot(x) | cotangent |
| sin(x) | sine |
| tan(x) | tangent |
