Mathematical Functions and Operators
Mathematical operators are provided for many Tacnode types. For types without standard mathematical conventions (e.g., date/time types) we describe the actual behavior in subsequent sections.
The table below shows the mathematical operators that are available for the standard numeric types. Unless otherwise noted, operators shown as accepting numeric_type are available for all the types smallint, integer, bigint, numeric, real, and double precision. Operators shown as accepting integral_type are available for the types smallint, integer, and bigint. Except where noted, each form of an operator returns the same data type as its argument(s). Calls involving multiple argument data types, such as integer + numeric, are resolved by using the type appearing later in these lists.
Mathematical Operators
| Operator | Description | Example(s) |
|---|---|---|
numeric_type + numeric_type | Addition | 2 + 3 returns 5 |
+ numeric_type | Unary plus (no operation) | + 3.5 returns 3.5 |
numeric_type - numeric_type | Subtraction | 2 - 3 returns -1 |
- numeric_type | Negation | - (-4) returns 4 |
numeric_type * numeric_type | Multiplication | 2 * 3 returns 6 |
numeric_type / numeric_type | Division (for integral types, division truncates the result towards zero) | 5.0 / 2 returns 2.5000000000000000; 5 / 2 returns 2; (-5) / 2 returns -2 |
numeric_type % numeric_type | Modulo (remainder); available for smallint, integer, bigint, and numeric | 5 % 4 returns 1 |
numeric ^ numeric | Exponentiation | 2 ^ 3 returns 8 |
double precision ^ double precision | Exponentiation. Unlike typical mathematical practice, multiple uses of ^ will associate left to right by default: 2 ^ 3 ^ 3 returns 512; 2 ^ (3 ^ 3) returns 134217728 | |
|/ double precision | Square root | |/ 25.0 returns 5 |
||/ double precision | Cube root | ||/ 64.0 returns 4 |
@ numeric_type | Absolute value | @ -5.0 returns 5 |
integral_type & integral_type | Bitwise AND | 91 & 15 returns 11 |
integral_type | integral_type | Bitwise OR | 32 | 3 returns 35 |
integral_type # integral_type | Bitwise exclusive OR | 17 # 5 returns 20 |
~ integral_type | Bitwise NOT | ~1 returns -2 |
integral_type << integer | Bitwise shift left | 1 << 4 returns 16 |
integral_type >> integer | Bitwise shift right | 8 >> 2 returns 2 |
The table below shows the available mathematical functions. 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(s); cross-type cases are resolved in the same way as explained above for operators. 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.
Mathematical Functions
| Function | Description | Example(s) |
|---|---|---|
abs ( numeric_type ) | Absolute value | abs(-17.4) returns 17.4 |
cbrt ( double precision ) | Cube root | cbrt(64.0) returns 4 |
ceil ( numeric ) | Nearest integer greater than or equal to argument | ceil(42.2) returns 43; ceil(-42.8) returns -42 |
ceil ( double precision ) | Same as above for double precision | |
ceiling ( numeric ) | Nearest integer greater than or equal to argument (same as ceil) | ceiling(95.3) returns 96 |
ceiling ( double precision ) | Same as above for double precision | |
degrees ( double precision ) | Converts radians to degrees | degrees(0.5) returns 28.64788975654116 |
div ( y numeric, x numeric ) | Integer quotient of y/x (truncates towards zero) | div(9, 4) returns 2 |
exp ( numeric ) | Exponential (e raised to the given power) | exp(1.0) returns 2.7182818284590452 |
exp ( double precision ) | Same as above for double precision | |
factorial ( bigint ) | Factorial | factorial(5) returns 120 |
floor ( numeric ) | Nearest integer less than or equal to argument | floor(42.8) returns 42; floor(-42.8) returns -43 |
floor ( double precision ) | Same as above for double precision | |
gcd ( numeric_type, numeric_type ) | Greatest common divisor (the largest positive number that divides both inputs with no remainder); returns 0 if both inputs are zero; available for integer, bigint, and numeric | gcd(1071, 462) returns 21 |
lcm ( numeric_type, numeric_type ) | Least common multiple (the smallest strictly positive number that is an integral multiple of both inputs); returns 0 if either input is zero; available for integer, bigint, and numeric | lcm(1071, 462) returns 23562 |
ln ( numeric ) | Natural logarithm | ln(2.0) returns 0.6931471805599453 |
ln ( double precision ) | Same as above for double precision | |
log ( numeric ) | Base 10 logarithm | log(100) returns 2 |
log ( double precision ) | Same as above for double precision | |
log10 ( numeric ) | Base 10 logarithm (same as log) | log10(1000) returns 3 |
log10 ( double precision ) | Same as above for double precision | |
log ( b numeric, x numeric ) | Logarithm of x to base b | log(2.0, 64.0) returns 6.0000000000 |
min_scale ( numeric ) | Minimum scale (number of fractional decimal digits) needed to represent the supplied value precisely | min_scale(8.4100) returns 2 |
mod ( y numeric_type, x numeric_type ) | Remainder of y/x; available for smallint, integer, bigint, and numeric | mod(9, 4) returns 1 |
pi ( ) | Approximate value of pi | pi() returns 3.141592653589793 |
power ( a numeric, b numeric ) | a raised to the power of b | power(9, 3) returns 729 |
power ( a double precision, b double precision ) | Same as above for double precision | |
radians ( double precision ) | Converts degrees to radians | radians(45.0) returns 0.7853981633974483 |
round ( numeric ) | Rounds to nearest integer. For numeric, ties are broken by rounding away from zero. For double precision, the tie-breaking behavior is platform dependent, but “round to nearest even” is the most common rule. | round(42.4) returns 42 |
round ( double precision ) | Same as above for double precision | |
round ( v numeric, s integer ) | Rounds v to s decimal places. Ties are broken by rounding away from zero. | round(42.4382, 2) returns 42.44 |
scale ( numeric ) | Scale of the argument (the number of decimal digits in the fractional part) | scale(8.4100) returns 4 |
sign ( numeric ) | Sign of the argument (-1, 0, or +1) | sign(-8.4) returns -1 |
sign ( double precision ) | Same as above for double precision | |
sqrt ( numeric ) | Square root | sqrt(2) returns 1.4142135623730951 |
sqrt ( double precision ) | Same as above for double precision | |
trim_scale ( numeric ) | Reduces the value’s scale (number of fractional decimal digits) by removing trailing zeroes | trim_scale(8.4100) returns 8.41 |
trunc ( numeric ) | Truncates to integer (towards zero) | trunc(42.8) returns 42; trunc(-42.8) returns -42 |
trunc ( double precision ) | Same as above for double precision | |
trunc ( v numeric, s integer ) | Truncates v to s decimal places | trunc(42.4382, 2) returns 42.43 |
width_bucket ( operand numeric, low numeric, high numeric, count integer ) | Returns the number of the bucket in which operand falls in a histogram having count equal-width buckets spanning the range low to high. Returns 0 or count+1 for an input outside that range. | width_bucket(5.35, 0.024, 10.06, 5) returns 3 |
width_bucket ( operand double precision, low double precision, high double precision, count integer ) | Same as above for double precision | |
width_bucket ( operand anycompatible, thresholds anycompatiblearray ) | Returns the number of the bucket in which operand falls given an array listing the lower bounds of the buckets. Returns 0 for an input less than the first lower bound. operand and the array elements can be of any type having standard comparison operators. The thresholds array must be sorted, smallest first, or unexpected results will be obtained. | width_bucket(now(), array['yesterday', 'today', 'tomorrow']::timestamptz[]) returns 2 |
Random Functions
The table below shows functions for generating random numbers.
| Function | Description | Example(s) |
|---|---|---|
random ( ) | Returns a random value in the range 0.0 (inclusive) to 1.0 (exclusive) | random() returns 0.897124072839091 |
setseed ( double precision ) | Sets the seed for subsequent random() calls; argument must be between -1.0 and 1.0, inclusive | setseed(0.12345) |
The random() function uses a simple linear congruential algorithm. It is fast but not suitable for cryptographic applications. If setseed() is called, the series of results of subsequent random() calls in the current session can be repeated by re-issuing setseed() with the same argument.
Trigonometric Functions
The table below shows the available trigonometric functions. Each of these functions comes in two variants, one that measures angles in radians and one that measures angles in degrees.
| Function | Description | Example(s) |
|---|---|---|
acos ( double precision ) | Inverse cosine, result in radians | acos(1) returns 0 |
acosd ( double precision ) | Inverse cosine, result in degrees | acosd(0.5) returns 60 |
asin ( double precision ) | Inverse sine, result in radians | asin(1) returns 1.5707963267948966 |
asind ( double precision ) | Inverse sine, result in degrees | asind(0.5) returns 30 |
atan ( double precision ) | Inverse tangent, result in radians | atan(1) returns 0.7853981633974483 |
atand ( double precision ) | Inverse tangent, result in degrees | atand(1) returns 45 |
atan2 ( y double precision, x double precision ) | Inverse tangent of y/x, result in radians | atan2(1, 0) returns 1.5707963267948966 |
atan2d ( y double precision, x double precision ) | Inverse tangent of y/x, result in degrees | atan2d(1, 0) returns 90 |
cos ( double precision ) | Cosine, argument in radians | cos(0) returns 1 |
cosd ( double precision ) | Cosine, argument in degrees | cosd(60) returns 0.5 |
cot ( double precision ) | Cotangent, argument in radians | cot(0.5) returns 1.830487721712452 |
cotd ( double precision ) | Cotangent, argument in degrees | cotd(45) returns 1 |
sin ( double precision ) | Sine, argument in radians | sin(1) returns 0.8414709848078965 |
sind ( double precision ) | Sine, argument in degrees | sind(30) returns 0.5 |
tan ( double precision ) | Tangent, argument in radians | tan(1) returns 1.5574077246549023 |
tand ( double precision ) | Tangent, argument in degrees | tand(45) returns 1 |
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).
Hyperbolic Functions
The table below shows the available hyperbolic functions.
| Function | Description | Example(s) |
|---|---|---|
sinh ( double precision ) | Hyperbolic sine | sinh(1) returns 1.1752011936438014 |
cosh ( double precision ) | Hyperbolic cosine | cosh(0) returns 1 |
tanh ( double precision ) | Hyperbolic tangent | tanh(1) returns 0.7615941559557649 |
asinh ( double precision ) | Inverse hyperbolic sine | asinh(1) returns 0.881373587019543 |
acosh ( double precision ) | Inverse hyperbolic cosine | acosh(1) returns 0 |
atanh ( double precision ) | Inverse hyperbolic tangent | atanh(0.5) returns 0.5493061443340548 |