This topic describes the functions supported by Hologres and provides use examples of these functions.
For more information about how to use string functions that are compatible with PostgreSQL, see Mathematical Functions and Operators.
Common mathematical functions
Function | Description | Example | Result |
abs(bigint) | Returns the absolute value of a number. The number is of the BIGINT data type and can be a literal or an expression that evaluates to a number. | abs(-17) | 17 |
abs(int) | Returns the absolute value of a number. The number is of the INT data type and can be a literal or an expression that evaluates to a number. | abs(-17) | 17 |
abs(float8) | Returns the absolute value of a number. The number is of the FLOAT8 data type and can be a literal or an expression that evaluates to a number. | abs(-17.4) | 17.4 |
abs(float4) | Returns the absolute value of a number. The number is of the FLOAT4 data type and can be a literal or an expression that evaluates to a number. | abs(-17.4) | 17.4 |
abs(numeric) | Returns the absolute value of a number. The number is of the NUMERIC data type and can be a literal or an expression that evaluates to a number. | abs(-17.4) | 17.4 |
cbrt(dp) | Returns the cube root of a number. The number is of the DOUBLE PRECISION data type and can be a literal or an expression that evaluates to a number. | cbrt(27.0) | 3.0000000000000004 |
ceil(dp ) | Rounds a number up to the nearest integer. The number is of the DOUBLE PRECISION data type and can be a literal or an expression that evaluates to a number. | ceil(-42.8) | -42.0 |
ceil(numeric) | Rounds a number up to the nearest integer. The number is of the NUMERIC data type and can be a literal or an expression that evaluates to a number. | ceil(-42.8) | -42.0 |
ceiling(dp ) | Rounds a number up to the nearest integer. The number is of the DOUBLE PRECISION data type and can be a literal or an expression that evaluates to a number. | ceil(-42.8) | -42.0 |
ceiling(numeric) | Rounds a number up to the nearest integer. The number is of the NUMERIC data type and can be a literal or an expression that evaluates to a number. | ceil(-42.8) | -42.0 |
degrees(float8) | Converts an angle in radians to its equivalent in degrees. The input parameter is a number of the FLOAT8 data type and can be a literal or an expression that evaluates to a number. | degrees(0.5) | 28.64788975654116 |
exp(float8) | Returns the result of constant e raised to the power of a number. The number is of the FLOAT8 data type and can be a literal or an expression that evaluates to a number. | exp(1.0) | 2.718281828459045 |
exp(numeric) | Returns the result of constant e raised to the power of a number. The number is of the NUMERIC data type and can be a literal or an expression that evaluates to a number. | exp(1.0) | 2.718281828459045 |
floor(float8) | Rounds a number down to the nearest integer. The number is of the FLOAT8 data type and can be a literal or an expression that evaluates to a number. | floor(-42.8) | -43.0 |
floor(numeric) | Rounds a number down to the nearest integer. The number is of the NUMERIC data type and can be a literal or an expression that evaluates to a number. | floor(-42.8) | -43.0 |
ln(float8) | Returns the natural logarithm of a number. The number is of the FLOAT8 data type and can be a literal or an expression that evaluates to a number. | ln(2.0) | 0.6931471805599453 |
ln(numeric) | Returns the natural logarithm of a number. The number is of the NUMERIC data type and can be a literal or an expression that evaluates to a number. | ln(2.0) | 0.6931471805599453 |
log(float8) | Returns the base 10 logarithm of a number. The number is of the FLOAT8 data type and can be a literal or an expression that evaluates to a number. | log(100.0) | 2.0 |
log(numeric) | Returns the base 10 logarithm of a number. The number is of the NUMERIC data type and can be a literal or an expression that evaluates to a number. | log(100.0) | 2.0 |
log(b numeric, x numeric) | Returns the base b logarithm of x. Both b and x are numbers, and the numbers are of the NUMERIC data type and can be literals or expressions that evaluate to numbers. | log(2.0, 64.0) | 6.0 |
mod(bigint, x) | Returns the remainder of a number divided by number x. The first number is of the BIGINT data type, and the two numbers can be literals or expressions that evaluate to numbers. | mod(9,4) | 1 |
mod(int, x) | Returns the remainder of a number divided by number x. The first number is of the INT data type, and the two numbers can be literals or expressions that evaluate to numbers. | mod(9,4) | 1 |
pi() | Returns the value of constant π to 15 decimal places. | pi() | 3.141592653589793 |
power(a float8, b float8) | Returns the result of a raised to the power of b. Both a and b are numbers of the FLOAT8 data type and can be literals or expressions that evaluates to numbers. | power(9.0, 3.0) | 729.0 |
power(a numeric, b numeric) | Returns the result of a raised to the power of b. Both a and b are numbers of the NUMERIC data type and can be literals or expressions that evaluate to numbers. | power(9.0, 3.0) | 729.0 |
radians(float8) | Converts an angle in degrees to its equivalent in radians. The input parameter is a number of the FLOAT8 data type and can be a literal or an expression that evaluates to a number. | radians(45.0) | 0.7853981633974483 |
random() | Returns a random number between 0.0 (inclusive) and 1.0 (exclusive). | random() | 0.3977345246821642 |
round(float8) | Rounds a number to the nearest integer. The number is of the FLOAT8 data type and can be a literal or an expression that evaluates to a number. | round(42.4) | 42.0 |
round(numeric) | Rounds a number to the nearest integer. The number is of the NUMERIC data type and can be a literal or an expression that evaluates to a number. | round(42.4) | 42.0 |
round(v numeric, s int) | Rounds v to the nearest number with s decimal places of precision. v is a number of the NUMERIC data type, and s is a number of the INT data type. v can be a literal or an expression that evaluates to a number. | round((100/400::numeric),4); | 0.2500 |
sign(float8) | Returns the sign of a number. The number of is of the FLOAT8 data type and can be a literal or an expression that evaluates to a number. If the specified number is greater than 0, the result of the function is 1. If the specified number is less than 0, the result of the function is -1. If the specified number is 0, the result of the function is 0. | sign(-8.4) | -1 |
sign(numeric) | Returns the sign of a number. The number is of the NUMERIC data type and can be a literal or an expression that evaluates to a number. If the specified number is greater than 0, the result of the function is 1. If the specified number is less than 0, the result of the function is -1. If the specified number is 0, the result of the function is 0. | sign(-8.4) | -1 |
sqrt(float8) | Returns the square root of a number. The number of is of the FLOAT8 data type and can be a literal or an expression that evaluates to a number. | sqrt(2.0) | 1.414213562373095 |
sqrt(numeric) | Returns the square root of a number. The number is of the NUMERIC data type and can be a literal or an expression that evaluates to a number. | sqrt(2.0) | 1.414213562373095 |
trunc(float8) | Truncates a number to an integer. The number is of the FLOAT8 data type and can be a literal or an expression that evaluates to a number. | trunc(42.8) | 42.0 |
trunc(numeric) | Truncates a number to an integer. The number is of the NUMERIC data type and can be a literal or an expression that evaluates to a number. | trunc(42.8) | 42.0 |
trunc(v numeric, s int) | Truncates v to s decimal places. v is a number of the NUMERIC data type, and s is a number of the INT data type. v can be a literal or an expression that evaluates to a number. | trunc(42.4382, 2) | 42.43 |
width_bucket(operand numeric, b1 numeric, ...) | Divides a data set into buckets of equal width and returns the bucket number into which an operand falls. | width_bucket(5.35, 0.024, 10.06, 5) | 3 |
Trigonometric functions
Function | Description | Example | Result |
acos(bigint) | Returns the arc cosine of a number. The number is of the BIGINT data type and can be a literal or an expression that evaluates to a number. | acos(1) | 0.0 |
acos(int) | Returns the arc cosine of a number. The number is of the INT data type and can be a literal or an expression that evaluates to a number. | acos(1) | 0.0 |
acos(float8) | Returns the arc cosine of a number. The number is of the FLOAT8 data type and can be a literal or an expression that evaluates to a number. | acos(0.9) | 0.45102681179626236 |
acos(float4) | Returns the arc cosine of a number. The number is of the FLOAT4 data type and can be a literal or an expression that evaluates to a number. | acos(0.9) | 0.45102681179626236 |
acos(numeric) | Returns the arc cosine of a number. The number is of the NUMERIC data type and can be a literal or an expression that evaluates to a number. | acos(0.9) | 0.45102681179626236 |
asin(bigint) | Returns the arc sine of a number. The number is of the BIGINT data type and can be a literal or an expression that evaluates to a number. | asin(1) | 1.5707963267948966 |
asin(int) | Returns the arc sine of a number. The number is of the INT data type and can be a literal or an expression that evaluates to a number. | asin(1) | 1.5707963267948966 |
asin(float8) | Returns the arc sine of a number. The number is of the FLOAT8 data type and can be a literal or an expression that evaluates to a number. | asin(1.0) | 1.5707963267948966 |
asin(float4) | Returns the arc sine of a number. The number is of the FLOAT4 data type and can be a literal or an expression that evaluates to a number. | asin(1.0) | 1.5707963267948966 |
asin(numeric) | Returns the arc sine of a number. The number is of the NUMERIC data type and can be a literal or an expression that evaluates to a number. | asin(1.0) | 1.5707963267948966 |
atan(bigint) | Returns the arc tangent of a number. The number is of the BIGINT data type and can be a literal or an expression that evaluates to a number. | atan(2) | 1.1071487177940904 |
atan(int) | Returns the arc tangent of a number. The number is of the INT data type and can be a literal or an expression that evaluates to a number. | atan(2) | 1.1071487177940904 |
atan(float8) | Returns the arc tangent of a number. The number is of the FLOAT8 data type and can be a literal or an expression that evaluates to a number. | atan(2.0) | 1.1071487177940904 |
atan(float4) | Returns the arc tangent of a number. The number is of the FLOAT4 data type and can be a literal or an expression that evaluates to a number. | atan(2.0) | 1.1071487177940904 |
atan(numeric) | Returns the arc tangent of a number. The number is of the NUMERIC data type and can be a literal or an expression that evaluates to a number. | atan(2.0) | 1.1071487177940904 |
atan2(bigint y, bigint x) | Returns the arc tangent of y divided by x. Both y and x are numbers of the BIGINT data type and can be literals or expressions that evaluate to numbers. | atan2(2, 1) | 1.1071487177940904 |
atan2(int y, int x) | Returns the arc tangent of y divided by x. Both y and x are numbers of the INT data type and can be literals or expressions that evaluate to numbers. | atan2(2, 1) | 1.1071487177940904 |
atan2(float8 y, float8 x) | Returns the arc tangent of y divided by x. Both y and x are numbers of the FLOAT8 data type and can be literals or expressions that evaluate to numbers. | atan2(2.0, 1.0) | 1.1071487177940904 |
atan2(float4 y, float4 x) | Returns the arc tangent of y divided by x. Both y and x are numbers of the FLOAT4 data type and can be literals or expressions that evaluate to numbers. | atan2(2.0, 1.0) | 1.1071487177940904 |
atan2(numeric y, numeric x) | Returns the arc tangent of y divided by x. Both y and x are numbers of the NUMERIC data type and can be literals or expressions that evaluate to numbers. | atan2(2.0, 1.0) | 1.1071487177940904 |
cos(bigint) | Returns the cosine of a number. The number is of the BIGINT data type and can be a literal or an expression that evaluates to a number. | cos(2) | -0.4161468365471424 |
cos(int) | Returns the cosine of a number. The number is of the INT data type and can be a literal or an expression that evaluates to a number. | cos(2) | -0.4161468365471424 |
cos(float8) | Returns the cosine of a number. The number is of the FLOAT8 data type and can be a literal or an expression that evaluates to a number. | cos(2.0) | -0.4161468365471424 |
cos(float4) | Returns the cosine of a number. The number is of the FLOAT4 data type and can be a literal or an expression that evaluates to a number. | cos(2.0) | -0.4161468365471424 |
cos(numeric) | Returns the cosine of a number. The number is of the NUMERIC data type and can be a literal or an expression that evaluates to a number. | cos(2.0) | -0.4161468365471424 |
cot(bigint) | Returns the cotangent of a number. The number is of the BIGINT data type and can be a literal or an expression that evaluates to a number. | cot(2) | -0.45765755436028577 |
cot(int) | Returns the cotangent of a number. The number is of the INT data type and can be a literal or an expression that evaluates to a number. | cot(2) | -0.45765755436028577 |
cot(float8) | Returns the cotangent of a number. The number is of the FLOAT8 data type and can be a literal or an expression that evaluates to a number. | cot(2.0) | -0.45765755436028577 |
cot(float4) | Returns the cotangent of a number. The number is of the FLOAT4 data type and can be a literal or an expression that evaluates to a number. | cot(2.0) | -0.45765755436028577 |
cot(numeric) | Returns the cotangent of a number. The number is of the NUMERIC data type and can be a literal or an expression that evaluates to a number. | cot(2.0) | -0.45765755436028577 |
sin(bigint) | Returns the sine of a number. The number is of the BIGINT data type and can be a literal or an expression that evaluates to a number. | sin(2) | 0.9092974268256817 |
sin(int) | Returns the sine of a number. The number is of the INT data type and can be a literal or an expression that evaluates to a number. | sin(2) | 0.9092974268256817 |
sin(float8) | Returns the sine of a number. The number is of the FLOAT8 data type and can be a literal or an expression that evaluates to a number. | sin(2.0) | 0.9092974268256817 |
sin(float4) | Returns the sine of a number. The number is of the FLOAT4 data type and can be a literal or an expression that evaluates to a number. | sin(2.0) | 0.909297268256817 |
sin(numeric) | Returns the sine of a number. The number is of the NUMERIC data type and can be a literal or an expression that evaluates to a number. | sin(2.0) | 0.9092974268256817 |
tan(bigint) | Returns the tangent of a number. The number is of the BIGINT data type and can be a literal or an expression that evaluates to a number. | tan(2) | -2.185039863261519 |
tan(int) | Returns the tangent of a number. The number is of the INT data type and can be a literal or an expression that evaluates to a number. | tan(2) | -2.185039863261519 |
tan(float8) | Returns the tangent of a number. The number is of the FLOAT8 data type and can be a literal or an expression that evaluates to a number. | tan(2.0) | -2.185039863261519 |
tan(float4) | Returns the tangent of a number. The number is of the FLOAT4 data type and can be a literal or an expression that evaluates to a number. | tan(2.0) | -2.185039863261519 |
tan(numeric) | Returns the tangent of a number. The number is of the NUMERIC data type and can be a literal or an expression that evaluates to a number. | tan(2.0) | -2.185039863261519 |