9.3. Mathematical Functions and Operators
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
(deprecated, use factorial() instead) | 5 ! | 120 |
!! | factorial as a prefix operator
(deprecated, use factorial() instead) | !! 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.14.
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 |
factorial(bigint )
| numeric | factorial | factorial(5) | 120 |
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 |
log10(dp or numeric )
| (same as input) | base 10 logarithm | log10(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 random()
function uses a simple linear
congruential algorithm. It is fast but not suitable for cryptographic
applications; see the pgcrypto module for a more
secure alternative.
If setseed()
is called, the results of
subsequent random()
calls in the current session are
repeatable by re-issuing setseed()
with the same
argument.
Without any prior setseed()
call in the same
session, the first random()
call obtains a seed
from a platform-dependent source of random bits.
Table 9.7 shows the
available trigonometric functions. All these 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)
.
Table 9.8 shows the
available hyperbolic functions. All these functions
take arguments and return values of type double
precision
.
Table 9.8. Hyperbolic Functions
Function | Description | Example | Result |
---|
sinh(x )
| hyperbolic sine | sinh(0) | 0 |
cosh(x )
| hyperbolic cosine | cosh(0) | 1 |
tanh(x )
| hyperbolic tangent | tanh(0) | 0 |
asinh(x )
| inverse hyperbolic sine | asinh(0) | 0 |
acosh(x )
| inverse hyperbolic cosine | acosh(1) | 0 |
atanh(x )
| inverse hyperbolic tangent | atanh(0) | 0 |