Mathematical functions reference with syntax and examples - Hologres

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

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);

 ceil
------
   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 NUMERIC value.

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