Use mathematical functions and operators - AnalyticDB - Alibaba Cloud Documentation Center

AnalyticDB for PostgreSQL is compatible with the mathematical functions and operators of PostgreSQL. This topic describes the mathematical functions and operators that are supported by AnalyticDB for PostgreSQL and provides examples on how to use the mathematical functions and operators.

For information about the mathematical functions and operators of PostgreSQL, see Mathematical Functions and Operators.

Mathematical operators

Mathematical operators are symbols or functions used in mathematics and programming languages to perform basic mathematical operations. The operators are used to perform numerical calculations, such as addition, subtraction, multiplication, division, and more complex mathematical operations.

Operator	Description	Example	Sample result
`+`	Performs the addition operation.	`2 + 3`	`5`
`-`	Performs the subtraction operation.	`2 - 3`	`-1`
`*`	Performs the multiplication operation.	`2 * 3`	`6`
`/`	Performs the division operation and returns a truncated integer result.	`4 / 2`	`2`
`%`	Performs the modulo operation and returns the remainder of a division.	`5 % 4`	`1`
`^`	The left-associative operator that performs the exponentiation operation.	`2.0 ^ 3.0`	`8`
`\|/`	Returns the square root of a number.	`\|/ 25.0`	`5`
`\|\|/`	Returns the cube root of a number.	`\|\|/ 27.0`	`3`
`!`	Returns the factorial of a number.	`5 !`	`120`
`!!`	The prefix operator that returns the factorial of a number.	`!! 5`	`120`
`@`	Returns the absolute value of a number.	`@ -5.0`	`5`
`&`	Returns the result of the bitwise AND operation between two operands.	`91 & 15`	`11`
`\|`	Returns the result of the bitwise OR operation between two operands.	`32 \| 3`	`35`
`#`	Returns the result of the bitwise XOR operation between two operands.	`17 # 5`	`20`
`~`	Returns the result of the bitwise NOT operation on an operand.	`~1`	`-2`
`<<`	Shifts the bits in the left operand to the left by the number of positions specified by the right operand and fills the vacant positions with zeros.	`1 << 4`	`16`
`>>`	Shifts the bits in the left operand to the right by the number of positions specified by the right operand and fills the vacant positions with zeros.	`8 >> 2`	`2`

Mathematical functions

Mathematical functions are a special class of mathematical expressions that map one or more independent variables to the values of the dependent variables. For example, in the linear function y = mx + b, m and b are constants, x is the independent variable, and y is the dependent variable. Other common functions include exponential functions, logarithmic functions, and trigonometric functions.

Function	Data type of the return value	Description	Example	Sample result
`abs(x)`	Same as the input value	Returns the absolute value of a number.	`abs(-17.4)`	`17.4`
`cbrt(DOUBLE PRECISION)`	`DOUBLE PRECISION`	Returns the cube root of a number.	`cbrt(27.0)`	`3`
`ceil(DOUBLE PRECISION or NUMERIC)`	Same as the input value	Rounds up to the nearest integer that is greater than or equal to the specified number.	`ceil(-42.8)`	`-42`
`ceiling(DOUBLE PRECISION or NUMERIC)`	Same as the input value	Rounds up to the nearest integer that is greater than or equal to the specified number. This function is the same as the `ceil` function.	`ceiling(-95.3)`	`-95`
`degrees(DOUBLE PRECISION)`	`DOUBLE PRECISION`	Converts a value in radians into degrees.	`degrees(0.5)`	`28.6478897565412`
`div(y NUMERIC, x NUMERIC)`	`NUMERIC`	Returns the integer quotient of `y` divided by `x`.	`div(9,4)`	`2`
`exp(DOUBLE PRECISION or NUMERIC)`	Same as the input value	Returns the exponential value of a number.	`exp(1.0)`	`2.71828182845905`
`floor(DOUBLE PRECISION or NUMERIC)`	Same as the input value	Rounds down to the nearest integer that is less than or equal to the specified number.	`floor(-42.8)`	`-43`
`ln(DOUBLE PRECISION or NUMERIC)`	Same as the input value	Returns the natural logarithm of a number.	`ln(2.0)`	`0.693147180559945`
`log(DOUBLE PRECISION or NUMERIC)`	Same as the input value	Returns the common logarithm (base-10 logarithm) of a number.	`log(100.0)`	`2`
`log10(DOUBLE PRECISION or NUMERIC)`	Same as the input value	Returns the common logarithm (base-10 logarithm) of a number. This function is the same as the log function.	`log10(100.0)`	`2`
`log(b NUMERIC,x NUMERIC)`	`NUMERIC`	Returns the logarithm of x to the base specified by `b`.	`log(2.0, 64.0)`	`6.0000000000`
`mod(y,x)`	Same as the input value	Returns the remainder of `y` divided by `x`.	`mod(9,4)`	`1`
`pi()`	`DOUBLE PRECISION`	Returns the value of the pi (π) constant.	`pi()`	`3.14159265358979`
`power(a DOUBLE PRECISION,b DOUBLE PRECISION)`	`DOUBLE PRECISION`	Returns the value of `a` to the power of `b`. a and b are of the DOUBLE PRECISION type.	`power(9.0, 3.0)`	`729`
`power(a NUMERIC,b NUMERIC)`	`NUMERIC`	Returns the value of `a` to the power of `b`. a and b are of the NUMERIC type.	`power(9.0, 3.0)`	`729`
`radians(DOUBLE PRECISION)`	`DOUBLE PRECISION`	Converts a value in degrees into radians.	`radians(45.0)`	`0.785398163397448`
`round(DOUBLE PRECISION or NUMERIC)`	Same as the input value	Rounds to the nearest integer.	`round(42.4)`	`42`
`round(v NUMERIC,s INT)`	`NUMERIC`	Rounds to `s` decimal places.	`round(42.4382, 2)`	`42.44`
`scale(NUMERIC)`	`INTEGER`	Returns the number of decimal digits in the fractional part of a number.	`scale(8.41)`	`2`
`sign(DOUBLE PRECISION or NUMERIC)`	Same as the input value	Returns the sign of a number. This function returns -1, 0, and +1 if the number is negative, zero, and positive.	`sign(-8.4)`	`-1`
`sqrt(DOUBLE PRECISION or NUMERIC)`	Same as the input value	Returns the square root of a number.	`sqrt(2.0)`	`1.4142135623731`
`trunc(DOUBLE PRECISION or NUMERIC)`	Same as the input value	Truncates toward zero.	`trunc(42.8)`	`42`
`trunc(v NUMERIC,s INT)`	`NUMERIC`	Truncates to `s` decimal places.	`trunc(42.4382, 2)`	`42.43`
`width_bucket(operand DOUBLE PRECISION,b1 DOUBLE PRECISION,b2 DOUBLE PRECISION,count INT)`	`int`	Returns the number of the bucket to which the `operand` is to be assigned in a histogram with `count` equal-width buckets that span the range from `b1` to `b2`. The operand, b1, and b2 arguments are of the DOUBLE PRECISION type. This function returns `0` or the value of `count+1` for an input value outside the range.	`width_bucket(5.35, 0.024, 10.06, 5)`	`3`
`width_bucket(operand NUMERIC,b1 NUMERIC,b2 NUMERIC,count INT)`	`INT`	Returns the number of the bucket to which the `operand` is to be assigned in a histogram with `count` equal-width buckets that span the range from `b1` to `b2`. The operand, b1, and b2 arguments are of the NUMERIC type. This function returns `0` or the value of `count+1` for an input value outside the range.	`width_bucket(5.35, 0.024, 10.06, 5)`	`3`
`width_bucket(operand anyelement,thresholds anyarray)`	`INT`	Returns the number of the bucket to which the `operand` is to be assigned based on an array of the lower bounds of the buckets specified by the thresholds argument. This function returns `0` for an input value that is less than the first lower bound. The array of the lower bounds specified by the `thresholds` argument must be sorted in ascending order, with the smallest value first. Otherwise, unexpected results may be returned.	`width_bucket(now(), array['yesterday', 'today', 'tomorrow']::timestamptz[])`	`2`

Random functions

A random function is a function of uncertainty whose output values can be one of the possible results instead of a specific, determined value.

Function	Data type of the return value	Description
`random()`	`DOUBLE PRECISION`	Returns a random value in the range of `0.0 (inclusive) to 1.0 (exclusive)`.
`setseed(dp)`	`Void`	Sets a seed for subsequent calls to the `random()` function. Valid values: -1.0 (inclusive) to 1.0 (inclusive).

Trigonometric functions

Trigonometric functions are a special class of functions that are related to the angles of right triangles. The most common trigonometric functions are sine (sin), cosine (cos), and tangent (tan). These functions can be used to calculate the magnitudes of angles in right triangles and solve more complex geometric problems.

Function (radians)	Function (degrees)	Description
`acos(x)`	`acosd(x)`	Returns the arccosine of x.
`asin(x)`	`asind(x)`	Returns the arcsine of x.
`atan(x)`	`atand(x)`	Returns the arctangent of x.
`atan2(y,x)`	`atan2d(y,x)`	Returns the arctangent of `y/x`.
`cos(x)`	`cosd(x)`	Returns the cosine of x.
`cot(x)`	`cotd(x)`	Returns the cotangent of x.
`sin(x)`	`sind(x)`	Returns the sine of x.
`tan(x)`	`tand(x)`	Returns the tangent of x.

Hyperbolic functions

Hyperbolic functions are a special class of functions that are related to hyperbolas. The most common hyperbolic functions are hyperbolic sine (sinh), hyperbolic cosine (cosh), and hyperbolic tangent (tanh). These functions can be used to solve complex mathematical problems, such as differential equations.

Function	Description	Example	Sample result
`sinh(x)`	Returns the hyperbolic sine of x.	`sinh(0)`	`0`
`cosh(x)`	Returns the hyperbolic cosine of x.	`cosh(0)`	`1`
`tanh(x)`	Returns the hyperbolic tangent of x.	`tanh(0)`	`0`
`asinh(x)`	Returns the inverse hyperbolic sine of x.	`asinh(0)`	`0`
`acosh(x)`	Returns the inverse hyperbolic cosine of x.	`acosh(1)`	`0`
`atanh(x)`	Returns the inverse hyperbolic tangent of x.	`atanh(0)`	`0`