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 and 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(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 |
scale(numeric )
|
integer |
scale of the argument (the number of decimal digits in the fractional part) |
scale(8.41) |
2 |
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.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(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.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(x )
|
acosd(x )
|
inverse cosine |
asin(x )
|
asind(x )
|
inverse sine |
atan(x )
|
atand(x )
|
inverse tangent |
atan2(y , x )
|
atan2d(y , x )
|
inverse tangent of y /x
|
cos(x )
|
cosd(x )
|
cosine |
cot(x )
|
cotd(x )
|
cotangent |
sin(x )
|
sind(x )
|
sine |
tan(x )
|
tand(x )
|
tangent |
Note
Another way to work with angles measured in degrees is to use the unit transformation functions radians()
and degrees()
shown earlier. However, using the degree-based trigonometric functions is preferred, as that way avoids round-off error for special cases such as sind(30)
.