Numerical functions

Numeric functions — these are functions for working with data of numeric types: BIGINT, NUMERIC and DOUBLE.

abs()

Description

Calculates the modulus of a number.
Usage

abs(num).
See examples 
SELECT
    abs(-1)  AS result_1,
    abs(0)   AS result_2,
    abs(1.1) AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 1        | 0        | 1.1      |
+----------+----------+----------+

add()

Description

adds numbers together.
Usage

add(num, num)

See also Operator +.

See examples 
SELECT
    add(1, 1)      AS result_1,
    add(-1.1, 2.1) AS result_2,
    add(1)         AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 2        | 1.0      | 1        |
+----------+----------+----------+

ceil()

Description

rounds a number to the higher side.
Usage

ceil(num)
Aliases

ceiling()
See examples 
SELECT
    ceil(0.1)  AS result_1,
    ceil(-0.1) AS result_2,
    ceiling(1) AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 1        | 0        | 1        |
+----------+----------+----------+

cos()

Description

Calculates the cosine of an angle given in radians.
Usage

cos(num)
See examples 
SELECT
    cos(0)      AS result_1,
    cos(pi())   AS result_2,
    cos(pi()/3) AS result_3;
+----------+----------+--------------------+
| result_1 | result_2 | result_3           |
+----------+----------+--------------------+
| 1        | -1       | 0.5000000000000001 |
+----------+----------+--------------------+

divide()

Description

Returns the result of division as an integer.
Usage

divide(num, num)
See examples 
SELECT
    divide(7, 2)  AS result_1,
    divide(7, -2) AS result_2,
    divide(7, 0)  AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 3        | -3       | null     |
+----------+----------+----------+

even()

Description

Rounds to the nearest even number away from zero.
Usage

even(num)
See examples 
SELECT
    even(2.1)  AS result_1,
    even(-2.1) AS result_2,
    even(0)    AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 4        | -4       | 0        |
+----------+----------+----------+

exp()

Description

Calculates the exponent of a number.
Usage

exp(num).

Calculates the exponential value of a number: .

See examples 
SELECT
    exp(0)  AS result_1,
    exp(1)  AS result_2,
    exp(-1) AS result_3;
+----------+-------------------+---------------------+
| result_1 | result_2          | result_3            |
+----------+-------------------+---------------------+
| 1        | 2.718281828459045 | 0.36787944117144233 |
+----------+-------------------+---------------------+

floor()

Description

rounds a number to the smaller side.
Usage

floor(num)
See examples 
SELECT
    floor(0.9)  AS result_1,
    floor(-0.9) AS result_2,
    floor(1)    AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 0        | -1       | 1        |
+----------+----------+----------+

fmod()

Description

Returns the remainder of dividing the first argument by the second argument.
Usage

fmod(num, num)
See examples 
SELECT
    fmod(3, 2)   AS result_1,
    fmod(3.1, 2) AS result_2,
    fmod(-10, 4) AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 1        | 1.1      | 2        |
+----------+----------+----------+

gcd()

Description

Calculates the greatest common divisor of two numbers.
Usage

gcd(num, num)
Aliases

greatest_common_divisor()
See examples 
SELECT
    gcd(12, 9)  AS result_1,
    gcd(-12, 9) AS result_2,
    gcd(12, 0)  AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 3        | 3        | 12       |
+----------+----------+----------+

greatest()

Description

Returns the largest number specified in the arguments.
Usage

greatest(num[, num, ...])
See examples 
SELECT
    greatest(1, 2, 3, 4, 4) AS result_1,
    greatest(1, -1)         AS result_2,
    greatest(0)             AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 4        | 1        | 0        |
+----------+----------+----------+

isfinite()

Description

Checks whether a number is finite.
Usage

isfinite(num)
See examples 
SELECT
    isfinite(1)                  AS result_1,
    isfinite('Infinity'::DOUBLE) AS result_2,
    isfinite(NULL)               AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| true     | false    | null     |
+----------+----------+----------+

isinf()

Description

Checks whether a number is infinite.
Usage

isinf(num)
See examples 
SELECT
    isinf(1)                  AS result_1,
    isinf('Infinity'::DOUBLE) AS result_2,
    isinf(NULL)               AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| false    | true     | null     |
+----------+----------+----------+

isnan()

Description

Checks if the argument has the value NaN (Not a Number).
Usage

isnan(num)
See examples 
SELECT
    isnan('NaN'::DOUBLE) AS result_1,
    isnan(1.1)           AS result_2,
    isnan(NULL)          AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| true     | false    | null     |
+----------+----------+----------+

lcm()

Description

Calculates the least common multiple of two numbers.
Usage

lcm(num, num)
Aliases

least_common_multiple()
See examples 
SELECT
    lcm(3, 7)     AS result_1,
    lcm(333, 777) AS result_2,
    lcm(37, 0)    AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 21       | 2331     | 0        |
+----------+----------+----------+

least()

Description

Returns the smallest number specified in the arguments.
Usage

least(num[, num, ...])
See examples 
SELECT
    least(1, 1, 2, 3, 4) AS result_1,
    least(1, -1)         AS result_2,
    least(0)             AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 1        | -1       | 0        |
+----------+----------+----------+

lgamma()

Description

Calculates the logarithm of the gamma function.
Usage

lgamma(num)
See examples 
SELECT
    lgamma(1)   AS result_1,
    lgamma(11)  AS result_2,
    lgamma(1.1) AS result_3;
+----------+--------------------+-----------------------+
| result_1 | result_2           | result_3              |
+----------+--------------------+-----------------------+
| 0        | 15.104412573075518 | -0.049872441259839764 |
+----------+--------------------+-----------------------+

ln()

Description

Calculates the natural logarithm of a number.
Usage

ln(num).
See examples 
SELECT
    ln(1)   AS result_1,
    ln(11)  AS result_2,
    ln(1.1) AS result_3;
+----------+--------------------+---------------------+
| result_1 | result_2           | result_3            |
+----------+--------------------+---------------------+
| 0        | 2.3978952727983707 | 0.09531017980432493 |
+----------+--------------------+---------------------+

log()

Description

Calculates the logarithm of a number on base 10.
Usage

log(num)
Aliases

log10()
See examples 
SELECT
    log(1)    AS result_1,
    log(100)  AS result_2,
    log(0.01) AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 0        | 2        | -2       |
+----------+----------+----------+

log2()

Description

Calculates the logarithm of a number on base 2.
Usage

log2(num)
See examples 
SELECT
    log2(1)    AS result_1,
    log2(2)    AS result_2,
    log2(4096) AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 0        | 1        | 12       |
+----------+----------+----------+

multiply()

Description

Multiplies two numbers.
Usage

multiply(num, num)

See also Operator *.

See examples 
SELECT
    multiply(2, 2)      AS result_1,
    multiply(0, 2)      AS result_2,
    multiply(0.2, -0.2) AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 4        | 0        | -0.04    |
+----------+----------+----------+

nextafter()

Description

Returns the next value with variable precision (of type DOUBLE) after the first number towards the second number.
Usage

nextafter(num, num)
See examples 
SELECT
    nextafter(1::DOUBLE, 2)  AS result_1,
    nextafter(1::BIGINT, 2)  AS result_2,
    nextafter(-1::BIGINT, 0) AS result_3;
+--------------------+--------------------+---------------------+
|      result_1      |      result_2      |       result_3      |
+--------------------+--------------------+---------------------+
| 1.0000000000000002 | 1.0000000000000002 | -0.9999999999999999 |
+--------------------+--------------------+---------------------+

pi()

Description

Returns the value of the number π.
Usage

pi()
See examples 
SELECT
    pi()   AS result_1,
    pi()/2 AS result_2,
    2*pi() AS result_3;
+-------------------+--------------------+-------------------+
|      result_1     |      result_2      |      result_3     |
+-------------------+--------------------+-------------------+
| 3.141592653589793 | 1.5707963267948966 | 6.283185307179586 |
+-------------------+--------------------+-------------------+

pow()

Description

Exposes the first argument to the degree given by the second argument.
Usage

pow(num, num)
Aliases

power()

See also Operator ^.

See examples 
SELECT
    pow(2, 5)   AS result_1,
    pow(25, -1) AS result_2,
    pow(25, 0)  AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 32       | 0.04     | 1        |
+----------+----------+----------+

radians()

Description

converts degrees to radians.
Usage

radians(num)
See examples 
SELECT
    radians(0)         AS result_1,
    radians(180)       AS result_2,
    radians(-180/pi()) AS result_3;
+----------+-------------------+----------+
| result_1 |      result_2     | result_3 |
+----------+-------------------+----------+
| 0        | 3.141592653589793 | -1       |
+----------+-------------------+----------+

random()

Description

Returns an arbitrary number (of type DOUBLE) between 0 and 1.
Usage

random()

See also setseed().

See examples 
SELECT
    random() AS result;
+--------------------+
|       result       |
+--------------------+
| 0.5656213557274057 |
+--------------------+

round_even()

Description

Round the number from the first argument to the nearest even number with the precision specified in the second argument.
Usage

round_even(num, num)
Aliases

roundbankers()

The second argument specifies the number of decimal places of precision and can be a negative number.

For more information on rounding to the nearest even number, see here.

See examples 
SELECT
    round_even(4.5, 0)    AS result_1,
    round_even(3.5, 0)    AS result_2,
    round_even(-4.5, 0)   AS result_3,
    round_even(-3.5, 0)   AS result_4,
    round_even(4.45, 1)   AS result_5,
    round_even(4.35, 1)   AS result_6,
    round_even(35.35, -1) AS result_7;
+----------+----------+----------+----------+----------+----------+----------+
| result_1 | result_2 | result_3 | result_4 | result_5 | result_6 | result_7 |
+----------+----------+----------+----------+----------+----------+----------+
| 4        | 4        | -4       | -4       | 4.4      | 4.4      | 40       |
+----------+----------+----------+----------+----------+----------+----------+

round()

Description

`rounds the number from the first argument to the precision specified in the second argument.
Usage

round(num, num)

The second argument specifies the number of decimal places of precision and can be a negative number.

See examples 
SELECT
    round(4.5, 0)   AS result_1,
    round(4.45, 1)  AS result_2,
    round(44.5, -1) AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 5        | 4.5      | 40       |
+----------+----------+----------+

setseed()

Description

Fixes the initial value for the random() function.
Usage

setseed(num)

See also random().

See examples 
SELECT
    setseed(0.5) AS seed,
    random() AS random;
+------+--------------------+
| seed | random             |
+------+--------------------+
| null | 0.8511131886287325 |
+------+--------------------+

sign()

Description

Returns -1, 1 or 0 depending on the sign of the argument.
Usage

sign(num)
See examples 
SELECT
    sign(10)  AS result_1,
    sign(-10) AS result_2,
    sign(0)   AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 1        | -1       | 0        |
+----------+----------+----------+

signbit()

Description

Determines whether the sign bit of a real number is set.
Usage

signbit(num)
See examples 
SELECT
    signbit(-1)                  AS result_1,
    signbit(-'Infinity'::DOUBLE) AS result_2,
    signbit(0)                   AS result_3,
    signbit(1)                   AS result_4,
    signbit('Infinity'::DOUBLE)  AS result_5;
+----------+----------+----------+----------+----------+
| result_1 | result_2 | result_3 | result_4 | result_5 |
+----------+----------+----------+----------+----------+
| true     | true     | false    | false    | false    |
+----------+----------+----------+----------+----------+

sin()

Description

Calculates the sine of an angle given in radians.
Usage

sin(num)
See examples 
SELECT
    sin(0)          AS result_1,
    sin(pi()/2)     AS result_2,
    sin((3*pi())/2) AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 0        | 1        | -1       |
+----------+----------+----------+

sqrt()

Description

Calculates the square root.
Usage

sqrt(num)

The number num must be non-negative.

See examples 
SELECT
    sqrt(4)   AS result_1,
    sqrt(144) AS result_2,
    sqrt(0)   AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 2        | 12       | 0        |
+----------+----------+----------+

subtract()

Description

Subtracts the second argument from the first argument.
Usage

subtract(num, num)

See also Operator -.

See examples 
SELECT
    subtract(1, 2)     AS result_1,
    subtract(1.1, 2.2) AS result_2,
    subtract(-1, -2)   AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| -1       | -1.1     | 1        |
+----------+----------+----------+

trunc()

Description

Discards all characters after the decimal separator.
Usage

trunc(num)

Not to be confused with rounding round.

See examples 
SELECT
    trunc(1.99)  AS result_1,
    trunc(-11.9) AS result_2,
    trunc(0.119) AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 1        | -11      | 0        |
+----------+----------+----------+

Operator +

Description

Adds the right argument to the left argument.
Usage

<num> + <num> [+ ...] or TIME + INTERVAL [+ ...].

If used with types for for date and time, adds the interval to the time value. Returns a value of type TIME.

See also add().

See examples 
SELECT
    3 + 2      AS result_1,
    3 + 2 + -1 AS result_2,
    1.1 + 1.9  AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 5        | 4        | 3.0      |
+----------+----------+----------+
SELECT
    TIME '12:11:10'     + INTERVAL 3 hours AS result_time_1,
    INTERVAL '12:11:10' + TIME '1:1:1'     AS result_time_2;
+---------------+---------------+
| result_time_1 | result_time_2 |
+---------------+---------------+
| 15:11:10      | 13:12:11      |
+---------------+---------------+

Operator -

Description

Subtracts the right argument from the left argument.
Usage

<num> - <num> [-...] or TIME - INTERVAL [-...].

If used with types for for date and time, subtracts the interval from the time value. Returns a value of type TIME.

See also subtract().

See examples 
SELECT
    3 - 2      AS result_1,
    3 - 2 - +1 AS result_2,
    1.2 - 0.2  AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 1        | 0        | 1.0      |
+----------+----------+----------+
SELECT
    TIME '12:11:10' - INTERVAL 3 HOUR AS result_time_1,
    TIME '12:11:10' - INTERVAL 3 HOUR - INTERVAL 1 HOUR AS result_time_2;
+---------------+---------------+
| result_time_1 | result_time_2 |
+---------------+---------------+
| 09:11:10      | 08:11:10      |
+---------------+---------------+

Operator *

Description

Multiplies the arguments.
Usage

<num> * <num>[ * <num>, {…​].

See also multiply().

See examples 
SELECT
    3*2     AS result_1,
    3*+2*-2 AS result_2,
    3*0     AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 6        | -12      | 0        |
+----------+----------+----------+

Operator /

Description

Divides the left argument by the right argument.
Usage

<num> / <num>[ / <num>,...]

Returns the result as a number with variable precision (of type DOUBLE).

See examples 
SELECT
    3/2     AS result_1,
    3/+2/-2 AS result_2,
    3/1     AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 1.5      | -0.75    | 3        |
+----------+----------+----------+

Operator %

Description

Returns the remainder of the left argument divided by the right argument.
Usage

<num> % <num>[ % <num>, { {…​].
See examples 
SELECT
    3 % 2       AS result_1,
    15 % 10 % 3 AS result_2,
    5 % 2.4     AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 1        | 2        | 0.2      |
+----------+----------+----------+

Operator ^

Description

Elevates the left argument to the degree given by the right argument.
Usage

<num> ^ <num>[ ^ <num>, ...]

See also pow().

See examples 
SELECT
    2^3   AS result_1,
    2^3^2 AS result_2,
    1^0   AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 8        | 64       | 1        |
+----------+----------+----------+