8.3. Mathematical Functions and Operators

Mathematical operators are provided for many EnterpriseDB 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 8-2 shows the available mathematical operators.

Table 8-2. Mathematical Operators

Operator	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`
`&`	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, whereas the others are available for all numeric data types.

Table 8-3 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. 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 may therefore vary depending on the host system.

Table 8-3. Mathematical Functions

Function	Return Type	Description	Example	Result
`abs(x)`	(same as `x`)	absolute value	`abs(-17.4)`	`17.4`
`cbrt(DOUBLE PRECISION)`	`DOUBLE PRECISION`	cube root	`cbrt(27.0)`	`3`
`ceil(DOUBLE PRECISION or NUMERIC)`	(same as input)	smallest integer not less than argument	`ceil(-42.8)`	`-42`
`ceiling(DOUBLE PRECISION or NUMERIC)`	(same as input)	smallest integer not less than argument (alias for `ceil`)	`ceiling(-95.3)`	`-95`
`degrees(DOUBLE PRECISION)`	`DOUBLE PRECISION`	radians to degrees	`degrees(0.5)`	`28.6478897565412`
`exp(DOUBLE PRECISION or NUMERIC)`	(same as input)	exponential	`exp(1.0)`	`2.71828182845905`
`floor(DOUBLE PRECISION or NUMERIC)`	(same as input)	largest integer not greater than argument	`floor(-42.8)`	`-43`
`ln(DOUBLE PRECISION or NUMERIC)`	(same as input)	natural logarithm	`ln(2.0)`	`0.693147180559945`
`log(DOUBLE PRECISION 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`
`nvl(x, y)`	(same as argument types; where both arguments are of the same datatype)	if `x` is null, then nvl returns`y`	`nvl(9,0)`	`9`
`mod(y, x)`	(same as argument types)	remainder of `y`/`x`	`mod(9,4)`	`1`
`pi()`	`DOUBLE PRECISION`	"π" constant	`pi()`	`3.14159265358979`
`power(a DOUBLE PRECISION, b DOUBLE PRECISION)`	`DOUBLE PRECISION`	`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(DOUBLE PRECISION)`	`DOUBLE PRECISION`	degrees to radians	`radians(45.0)`	`0.785398163397448`
`random()`	`DOUBLE PRECISION`	random value between 0.0 and 1.0	`random()`
`round(DOUBLE PRECISION or NUMERIC)`	(same as input)	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`
`setseed(DOUBLE PRECISION)`	`INTEGER`	set seed for subsequent `random()` calls	`setseed(0.54823)`	`1177314959`
`sign(DOUBLE PRECISION or NUMERIC)`	(same as input)	sign of the argument (-1, 0, +1)	`sign(-8.4)`	`-1`
`sqrt(DOUBLE PRECISION or NUMERIC)`	(same as input)	square root	`sqrt(2.0)`	`1.4142135623731`
`trunc(DOUBLE PRECISION or NUMERIC)`	(same as input)	truncate toward zero	`trunc(42.8)`	`42`
`trunc(v NUMERIC, s INTEGER)`	`NUMERIC`	truncate to `s` decimal places	`trunc(42.4382, 2)`	`42.43`
`width_bucket(op NUMERIC, b1 NUMERIC, b2 NUMERIC, count INTEGER)`	`INTEGER`	return the bucket to which `operand` would be assigned in an equidepth histogram with `count` buckets, an upper bound of `b1`, and a lower bound of `b2`	`width_bucket(5.35, 0.024, 10.06, 5)`	`3`

Finally, Table 8-4 shows the available trigonometric functions. All trigonometric functions take arguments and return values of type double precision.

Table 8-4. 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