DocsICP FilingConsole
Documentation
Home Page Hologres Developer Reference Function Reference Mathematical functions

Mathematical functions

Updated at:
Copy as MD
Product
My Favorites

Hologres supports the following mathematical functions. For functions not listed here, refer to Mathematical Functions and Operators in the PostgreSQL 11 documentation.

Note

Example outputs in this topic use the default display precision of the psql client (extra_float_digits = 0). The number of digits shown for floating-point results varies with the client extra_float_digits setting; the underlying values are the same.

Common mathematical functions

Function Description
ABS Returns the absolute value of a number.
CBRT Returns the cube root of a number.
CEIL Rounds a number up to the nearest integer. Alias of CEILING.
CEILING Rounds a number up to the nearest integer. Alias of CEIL.
DEGREES Converts radians to degrees.
EXP Returns e raised to the power of a number (e^num).
FLOOR Rounds a number down to the nearest integer.
LN Returns the natural logarithm of a number.
LOG Returns the base-10 logarithm of a number.
MOD Returns the remainder after dividing one number by another.
PI Returns the value of π (3.14159265358979).
POWER Returns a raised to the power of b.
RADIANS Converts degrees to radians.
RANDOM Returns a random number in [0.0, 1.0).
ROUND Rounds a number to the nearest integer or to a specified number of decimal places.
SIGN Returns the sign of a number: 1, -1, or 0.
SQRT Returns the square root of a number.
TRUNC Truncates a number to an integer or to a specified number of decimal places.
WIDTH_BUCKET Assigns a value to one of equal-width buckets and returns the bucket number.

Trigonometric functions

Function Description
ACOS Returns the arccosine of a number.
ASIN Returns the arcsine of a number.
ATAN Returns the arctangent of a number.
ATAN2 Returns the arctangent of y/x.
COS Returns the cosine of a number.
COT Returns the cotangent of a number.
SIN Returns the sine of a number.
TAN Returns the tangent of a number.

ABS

Returns the absolute value of a number.

Syntax

ABS(num)

Parameters

Parameter Required Type Description
num Yes INT, BIGINT, REAL, DOUBLE PRECISION, NUMERIC, or DECIMAL The input number.

Return value

Returns a value of the same type as the input.

Example

SELECT ABS(-17.4);
 abs
-----
 17.4

CBRT

Returns the cube root of a number.

Syntax

CBRT(num)

Parameters

Parameter Required Type Description
num Yes DOUBLE PRECISION The input number.

Return value

Returns a DOUBLE PRECISION value.

Example

SELECT CBRT(9);
      cbrt
-----------------
 2.0800838230519

CEIL

Rounds a number up to the nearest integer. CEIL is an alias of CEILING.

Syntax

CEIL(num)

Parameters

Parameter Required Type Description
num Yes DOUBLE PRECISION or NUMERIC The input number.

Return value

Returns a value of the same type as the input.

Example

SELECT CEIL(9.2);
 ceil
------
   10

See also

CEILING, FLOOR, ROUND, TRUNC

CEILING

Rounds a number up to the nearest integer. CEILING is an alias of CEIL.

Syntax

CEILING(num)

Parameters

Parameter Required Type Description
num Yes DOUBLE PRECISION or NUMERIC The input number.

Return value

Returns a value of the same type as the input.

Example

SELECT CEILING(9.2);
 ceiling
---------
      10

See also

CEIL, FLOOR, ROUND, TRUNC

DEGREES

Converts a value in radians to degrees.

Syntax

DEGREES(num)

Parameters

Parameter Required Type Description
num Yes DOUBLE PRECISION The input value in radians.

Return value

Returns a DOUBLE PRECISION value representing the equivalent angle in degrees.

Example

SELECT DEGREES(3.2);
      degrees
--------------
 183.34649444186343

See also

RADIANS

EXP

Returns e raised to the power of num (e^num).

Syntax

EXP(num)

Parameters

Parameter Required Type Description
num Yes DOUBLE PRECISION or NUMERIC The exponent.

Return value

Returns a value of the same type as the input.

Example

SELECT EXP(1);
             exp
----------------
 2.718281828459045

FLOOR

Rounds a number down to the nearest integer.

Syntax

FLOOR(num)

Parameters

Parameter Required Type Description
num Yes DOUBLE PRECISION or NUMERIC The input number.

Return value

Returns a value of the same type as the input.

Example

SELECT FLOOR(3.8);
 floor
------
     3

See also

CEIL, CEILING, ROUND, TRUNC

LN

Returns the natural logarithm of a number.

Syntax

LN(num)

Parameters

Parameter Required Type Description
num Yes DOUBLE PRECISION or NUMERIC The input number.

Return value

Returns a value of the same type as the input.

Example

SELECT LN(3.8);
            ln
----------------
 1.3350010667323401

LOG

Returns the base-10 logarithm of a number.

Syntax

LOG(num)

Parameters

Parameter Required Type Description
num Yes DOUBLE PRECISION or NUMERIC The input number.

Return value

Returns a value of the same type as the input.

Example

SELECT LOG(100);
 log
-----
   2

MOD

Returns the remainder after dividing num by x.

Syntax

MOD(num, x)

Parameters

Parameter Required Type Description
num Yes NUMERIC The dividend.
x Yes NUMERIC The divisor.

Return value

Returns a value of the same type as the input.

Example

SELECT MOD(9, 4);
 mod
-----
   1

PI

Returns the value of π.

Syntax

PI()

Return value

Returns the DOUBLE PRECISION value 3.14159265358979.

Example

SELECT PI();
        pi
------------------
 3.14159265358979

POWER

Returns a raised to the power of b.

Syntax

POWER(a, b)

Parameters

Parameter Required Type Description
a Yes DOUBLE PRECISION The base.
b Yes DOUBLE PRECISION The exponent.

Return value

Returns a DOUBLE PRECISION value.

Example

SELECT POWER(9, 3);
 power
-------
   729

RADIANS

Converts a value in degrees to radians.

Syntax

RADIANS(num)

Parameters

Parameter Required Type Description
num Yes DOUBLE PRECISION The input value in degrees.

Return value

Returns a DOUBLE PRECISION value representing the equivalent angle in radians.

Example

SELECT RADIANS(45);
       radians
-------------------
 0.785398163397448

See also

DEGREES

RANDOM

Returns a random DOUBLE PRECISION value in the range [0.0, 1.0).

Syntax

RANDOM()

Return value

Returns a DOUBLE PRECISION value. Valid values: [0.0, 1.0).

Example

SELECT RANDOM();
      random
-------------------
 0.377819478977472

ROUND

Rounds a number to the nearest integer, or to a specified number of decimal places.

Syntax

-- Round to the nearest integer
ROUND(num)

-- Round to s decimal places
ROUND(num, s)

Parameters

Parameter Required Type Description
num Yes DOUBLE PRECISION or NUMERIC (single-argument form); NUMERIC only (two-argument form) The input number.
s No INT The number of decimal places to round to.

Return value

Returns a value of the same type as num.

Example

SELECT ROUND(42.4);
 round
-------
    42

See also

CEIL, CEILING, FLOOR, TRUNC

SIGN

Returns the sign of a number: 1 if positive, -1 if negative, or 0 if zero.

Syntax

SIGN(num)

Parameters

Parameter Required Type Description
num Yes DOUBLE PRECISION or NUMERIC The input number.

Return value

Returns 1, -1, or 0.

Example

SELECT SIGN(-8.4);
 sign
------
   -1

SQRT

Returns the square root of a number.

Syntax

SQRT(num)

Parameters

Parameter Required Type Description
num Yes DOUBLE PRECISION or NUMERIC The input number.

Return value

Returns a value of the same type as the input.

Example

SELECT SQRT(2);
      sqrt
-----------------
 1.4142135623731

TRUNC

Truncates a number toward zero, discarding fractional digits.

Syntax

-- Truncate to an integer
TRUNC(num)

-- Truncate to s decimal places
TRUNC(num, s)

Parameters

Parameter Required Type Description
num Yes NUMERIC The input number.
s No INT The number of decimal places to keep.

Return value

Returns a NUMERIC value.

Example

SELECT TRUNC(2.456);
 trunc
-------
     2
SELECT TRUNC(2.4564, 2);
 trunc
-------
  2.45

See also

CEIL, CEILING, FLOOR, ROUND

WIDTH_BUCKET

Divides a range into equal-width buckets and returns the bucket number that a value falls into.

Syntax

WIDTH_BUCKET(value, start, end, num_buckets)

Parameters

Parameter Required Type Description
value Yes DOUBLE PRECISION or NUMERIC The value to assign to a bucket.
start Yes DOUBLE PRECISION or NUMERIC The lower bound of the range.
end Yes DOUBLE PRECISION or NUMERIC The upper bound of the range.
num_buckets Yes INT The number of equal-width buckets to create.

Return value

Returns an INT value.

Example

The following example divides the range 0–100 into 5 equal buckets (0–20, 20–40, 40–60, 60–80, 80–100) and returns the bucket number for the value 45.

SELECT WIDTH_BUCKET(45, 0, 100, 5);
 width_bucket
--------------
            3

The value 45 falls in bucket 3 (the range 40–60).

ACOS

Returns the arccosine of num.

Syntax

ACOS(num)

Parameters

Parameter Required Type Description
num Yes NUMERIC The input value. Valid values: [-1, 1].

Return value

Returns a DOUBLE PRECISION value.

Example

SELECT ACOS(-1);
       acos
------------------
 3.14159265358979

See also

ASIN, ATAN, ATAN2, COS

ASIN

Returns the arcsine of num.

Syntax

ASIN(num)

Parameters

Parameter Required Type Description
num Yes DOUBLE PRECISION The input value. Valid values: [-1, 1].

Return value

Returns a DOUBLE PRECISION value.

Example

SELECT ASIN(-1);
       asin
------------------
 -1.5707963267949

See also

ACOS, ATAN, ATAN2, SIN

ATAN

Returns the arctangent of num.

Syntax

ATAN(num)

Parameters

Parameter Required Type Description
num Yes DOUBLE PRECISION The input value.

Return value

Returns a DOUBLE PRECISION value.

Example

SELECT ATAN(2);
       atan
------------------
 1.10714871779409

See also

ACOS, ASIN, ATAN2, TAN

ATAN2

Returns the arctangent of y/x, using the signs of both arguments to determine the correct quadrant.

Syntax

ATAN2(y, x)

Parameters

Parameter Required Type Description
y Yes DOUBLE PRECISION The y-coordinate.
x Yes DOUBLE PRECISION The x-coordinate.

Return value

Returns a DOUBLE PRECISION value.

Example

SELECT ATAN2(4, 2);
      atan2
------------------
 1.10714871779409

See also

ATAN

COS

Returns the cosine of num (input in radians).

Syntax

COS(num)

Parameters

Parameter Required Type Description
num Yes DOUBLE PRECISION The input angle in radians.

Return value

Returns a DOUBLE PRECISION value.

Example

SELECT COS(2);
        cos
--------------------
 -0.416146836547142

See also

ACOS, SIN, TAN

COT

Returns the cotangent of num (input in radians).

Syntax

COT(num)

Parameters

Parameter Required Type Description
num Yes DOUBLE PRECISION The input angle in radians.

Return value

Returns a DOUBLE PRECISION value.

Example

SELECT COT(2);
        cot
--------------------
 -0.457657554360286

See also

TAN

SIN

Returns the sine of num (input in radians).

Syntax

SIN(num)

Parameters

Parameter Required Type Description
num Yes DOUBLE PRECISION The input angle in radians.

Return value

Returns a DOUBLE PRECISION value.

Example

SELECT SIN(2);
        sin
-------------------
 0.909297426825682

See also

ASIN, COS, TAN

TAN

Returns the tangent of num (input in radians).

Syntax

TAN(num)

Parameters

Parameter Required Type Description
num Yes DOUBLE PRECISION The input angle in radians.

Return value

Returns a DOUBLE PRECISION value.

Example

SELECT TAN(2);
        tan
-------------------
 -2.18503986326152

See also

ATAN, ATAN2, COT, SIN, COS

Previous：KeyvalueNext：Pattern matching functions