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      |
+----------+----------+----------+

acos()

Description

Calculates the arccosine of a number.
Usage

acos(num)

Calculates the arccosine of a number — returns the angle in radians whose cosine is equal to the given number.

See examples 
SELECT
    acos(1)  AS result_1,
    acos(-1) AS result_2,
    acos(0)  AS result_3;
+----------+-------------------+--------------------+
| result_1 | result_2          | result_3           |
+----------+-------------------+--------------------+
| 0        | 3.141592653589793 | 1.5707963267948966 |
+----------+-------------------+--------------------+

acosh()

Description

Calculates the hyperbolic arccosine of a number.
Usage

acosh(num)
See examples 
SELECT
    acosh(1)     AS result_1,
    acosh(1.543) AS result_2,
    acosh(10)    AS result_3;
+----------+-------------------+-------------------+
| result_1 | result_2          | result_3          |
+----------+-------------------+-------------------+
| 0        | 0.999931383282944 | 2.993222846126381 |
+----------+-------------------+-------------------+

add()

Description

Adds numbers.
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        |
+----------+----------+----------+

asin()

Description

Calculates the arcsine of a number.
Usage

asin(num)

Calculates the arcsine of a number — returns the angle in radians whose sine is equal to the given number.

See examples 
SELECT
    asin(0)   AS result_1,
    asin(0.5) AS result_2,
    asin(1)   AS result_3;
+----------+--------------------+--------------------+
| result_1 | result_2           | result_3           |
+----------+--------------------+--------------------+
| 0        | 0.5235987755982989 | 1.5707963267948966 |
+----------+--------------------+--------------------+

asinh()

Description

Calculates the hyperbolic arcsine of a number.
Usage

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

atan()

Description

Calculates the arctangent of a number.
Usage

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

atan2()

Description

Calculates the arctangent from the coordinates.
Usage

atan2(y, x)

Calculates the arctangent by coordinates . Returns the angle in radians.

See examples 
SELECT
    atan2(0, 0)  AS result_1,
    atan2(0, -1) AS result_2,
    atan2(-1, 0) AS result_3;
+----------+-------------------+---------------------+
| result_1 | result_2          | result_3            |
+----------+-------------------+---------------------+
| 0        | 3.141592653589793 | -1.5707963267948966 |
+----------+-------------------+---------------------+

atanh()

Description

Calculates the hyperbolic arctangent of a number.
Usage

atanh(num)
See examples 
SELECT
    atanh(0.1)   AS result_1,
    atanh(0.5)   AS result_2,
    atanh(0.762) AS result_3;
+---------------------+--------------------+--------------------+
| result_1            | result_2           | result_3           |
+---------------------+--------------------+--------------------+
| 0.10033534773107558 | 0.5493061443340548 | 1.0009670664123251 |
+---------------------+--------------------+--------------------+

bit_count()

Description

Returns the number of bits set in the binary representation of a number.
Usage

bit_count(num)
See examples 
SELECT
    bit_count(1) AS result_1,
    bit_count(3) AS result_2,
    bit_count(7) AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 1        | 2        | 3        |
+----------+----------+----------+

cbrt()

Description

Calculates the cube root.
Usage

cbrt(num)
See examples 
SELECT
    cbrt(1)  AS result_1,
    cbrt(8)  AS result_2,
    cbrt(-8) AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 1        | 2        | -2       |
+----------+----------+----------+

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 |
+----------+----------+--------------------+

cot()

Description

Calculates the cotangent of an angle given in radians.
Usage

cot(num)
See examples 
SELECT
    cot(1)      AS result_1,
    cot(pi()/2) AS result_2,
    cot(pi()/4) AS result_3;
+--------------------+-----------------------+--------------------+
| result_1           | result_2              | result_3           |
+--------------------+-----------------------+--------------------+
| 0.6420926159343306 | 6.123233995736766e-17 | 1.0000000000000002 |
+--------------------+-----------------------+--------------------+

degrees()

Description

Converts radians to degrees.
Usage

degrees(num)
See examples 
SELECT
    degrees(0)      AS result_1,
    degrees(pi())   AS result_2,
    degrees(pi()/2) AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 0        | 180      | 90       |
+----------+----------+----------+

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 |
+----------+-------------------+---------------------+

factorial()

Description

Calculates the factorial of a number.
Usage

factorial(num)
See examples 
SELECT
    factorial(0) AS result_1,
    factorial(1) AS result_2,
    factorial(5) AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 1        | 1        | 120      |
+----------+----------+----------+

fdiv()

Description

Performs integer division and returns the result as DOUBLE.
Usage

fdiv(num, num)
See examples 
SELECT
    fdiv(5, 2)  AS result_1,
    fdiv(1, 2)  AS result_2,
    fdiv(-5, 2) AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 2        | 0        | -3       |
+----------+----------+----------+

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        |
+----------+----------+----------+

gamma()

Description

Calculates the value of the Gamma function.
Usage

gamma(num)

Calculates the value of the Gamma function (interpolation of the factorial ). Fractional (real) values are allowed.

See examples 
SELECT
    gamma(1)   AS result_1,
    gamma(5)   AS result_2,
    gamma(0.5) AS result_3;
+----------+----------+-------------------+
| result_1 | result_2 | result_3          |
+----------+----------+-------------------+
| 1        | 24       | 1.772453850905516 |
+----------+----------+-------------------+

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 of the specified arguments.
Usage

greatest(argument[, argument, ...])

  • When used with numbers, returns the largest number.

  • When used with strings — returns the last of the strings in alphabetical order. In this case, upper case precedes lower case.

  • When used with types of date and time — returns the most recent object.

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        |
+----------+----------+----------+
SELECT
    greatest('a', 'z', 'A', 'Z') AS result_1,
    greatest('Tengri', 'TNGRi')  AS result_2;
+----------+----------+
| result_1 | result_2 |
+----------+----------+
| z        | Tengri   |
+----------+----------+
SELECT
    greatest(DATE '2024-02-29', TIMESTAMP '2024-02-29 00:00:01') AS result_1,
    greatest(DATE '2024-02-29', DATE '2029-02-24')               AS result_2,
    greatest(DATE '2024-02-29', now())                           AS result_3;
+---------------------+------------+----------------------------------+
| result_1            | result_2   | result_3                         |
+---------------------+------------+----------------------------------+
| 2024-02-29T00:00:01 | 2029-02-24 | 2026-05-06T13:05:46.173330+03:00 |
+---------------------+------------+----------------------------------+

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 of the specified arguments.
Usage

least(argument[, argument, ...])

  • When used with numbers, returns the smallest number.

  • When used with strings — returns the first of the strings in alphabetical order. In this case, upper case precedes lower case.

  • When used with types date and time — returns the earliest object.

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        |
+----------+----------+----------+
SELECT
    least(DATE '2024-02-29', TIMESTAMP '2024-02-29 00:00:01') AS result_1,
    least(DATE '2024-02-29', DATE '2029-02-24')               AS result_2,
    least(DATE '2024-02-29', now())                           AS result_3;
+---------------------+------------+---------------------------+
| result_1            | result_2   | result_3                  |
+---------------------+------------+---------------------------+
| 2024-02-29T00:00:00 | 2024-02-29 | 2024-02-29T00:00:00+03:00 |
+---------------------+------------+---------------------------+
SELECT
    least('a', 'z', 'A', 'Z') AS result_1,
    least('Tengri', 'TNGRi')  AS result_2;
+----------+----------+
| result_1 | result_2 |
+----------+----------+
| A        | TNGRi    |
+----------+----------+

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,
    sin(pi()/2) AS result_3;
+-------------------+--------------------+----------+
| result_1          | result_2           | result_3 |
+-------------------+--------------------+----------+
| 3.141592653589793 | 1.5707963267948966 | 1        |
+-------------------+--------------------+----------+

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 |
+--------------------+
The random() function is convenient for selecting a random sample of a specified length from the table.

Let’s show by example how to select a random sample of size 10 from a table with 1 million rows:

CREATE TABLE demo.numbers (numbers BIGINT);

INSERT INTO demo.numbers (numbers)
    SELECT unnest(generate_series(1,1000000));

SELECT * FROM demo.numbers
ORDER BY random()
LIMIT 10;
+---------+
| numbers |
+---------+
| 265453  |
+---------+
| 16766   |
+---------+
| 861234  |
+---------+
| 455059  |
+---------+
| 898869  |
+---------+
| 454774  |
+---------+
| 465029  |
+---------+
| 885538  |
+---------+
| 465844  |
+---------+
| 50905   |
+---------+

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        |
+----------+----------+----------+

tan()

Description

Calculates the tangent of an angle given in radians.
Usage

tan(num)
See examples 
SELECT
    tan(0)      AS result_1,
    tan(pi()/4) AS result_2,
    tan(pi())   AS result_3;
+----------+--------------------+-------------------------+
| result_1 | result_2           | result_3                |
+----------+--------------------+-------------------------+
| 0        | 0.9999999999999999 | -1.2246467991473532e-16 |
+----------+--------------------+-------------------------+

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        |
+----------+----------+----------+

xor()

Description

Computes a bitwise exclusive OR.
Usage

xor(num, num)
See examples 
SELECT
    xor(2, 3) AS result_1,
    xor(5, 7) AS result_2,
    xor(1, 2) AS result_3;
+----------+----------+----------+
| result_1 | result_2 | result_3 |
+----------+----------+----------+
| 1        | 2        | 3        |
+----------+----------+----------+

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().

Not to be confused with another asterisk.

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        |
+----------+----------+----------+

Operator <

Description

Checks that the left argument is less than the right argument.
Usage

<num> < <num>

Can be used not only with numeric types, but also with types for date and time.

See examples 
SELECT
    0 < 1    AS result_1,
    1 < 0    AS result_2, -- false expected
    1 < NULL AS result_3,
    '2025-01-01'::DATE < '2026-01-01'::DATE AS result_4,
    '00:00'::TIME < '15:00'::TIME           AS result_5;
+----------+----------+----------+----------+----------+
| result_1 | result_2 | result_3 | result_4 | result_5 |
+----------+----------+----------+----------+----------+
| true     | false    | null     | true     | true     |
+----------+----------+----------+----------+----------+

Operator >

Description

Checks that the left argument is greater than the right argument.
Usage

<num> > <num>

Can be used not only with numeric types, but also with types for date and time.

See examples 
SELECT
    1 > 0    AS result_1,
    0 > 1    AS result_2, -- false expected
    1 > NULL AS result_3,
    '2026-01-01'::DATE > '2025-01-01'::DATE AS result_4,
    '15:00'::TIME > '00:00'::TIME           AS result_5;
+----------+----------+----------+----------+----------+
| result_1 | result_2 | result_3 | result_4 | result_5 |
+----------+----------+----------+----------+----------+
| true     | false    | null     | true     | true     |
+----------+----------+----------+----------+----------+

Operator <=

Description

Checks that the left argument is less than or equal to the right argument.
Usage

<num> <= <num>

Can be used not only with numeric types, but also with types for date and time.

Use BETWEEN to check intervals.
See examples 
SELECT
    1 <= 1    AS result_1,
    1 <= 0    AS result_2, -- false expected
    1 <= NULL AS result_3,
    '2025-01-01'::DATE <= '2026-01-01'::DATE AS result_4,
    '00:00'::TIME <= '15:00'::TIME           AS result_5;
+----------+----------+----------+----------+----------+
| result_1 | result_2 | result_3 | result_4 | result_5 |
+----------+----------+----------+----------+----------+
| true     | false    | null     | true     | true     |
+----------+----------+----------+----------+----------+

Operator >=

Description

Checks that the left argument is greater than or equal to the right argument.
Usage

<num> >= <num>

Can be used not only with numeric types, but also with types for date and time.

Use BETWEEN to check intervals.
See examples 
SELECT
    1 >= 0    AS result_1,
    0 >= 1    AS result_2, -- false expected
    1 >= NULL AS result_3,
    '2026-01-01'::DATE >= '2025-01-01'::DATE AS result_4,
    '15:00'::TIME >= '00:00'::TIME           AS result_5;
+----------+----------+----------+----------+----------+
| result_1 | result_2 | result_3 | result_4 | result_5 |
+----------+----------+----------+----------+----------+
| true     | false    | null     | true     | true     |
+----------+----------+----------+----------+----------+

Operator =

Description

Checks that the left argument is equal to the right argument.
Usage

<num> = <num>
Aliases

==

Can be used not only with numeric types, but also with types for date and time.

See examples 
SELECT
    1 == 1   AS result_1,
    1 = 1    AS result_2,
    1 = 0    AS result_3, -- false expected
    1 = NULL AS result_4,
    '2026-01-01'::DATE = '2026-01-01 00:00'::DATE   AS result_5,
    '00:00'::TIME = '00:00:00'::TIME                AS result_6,
    '00:00'::TIME = '12:00'::TIME                   AS result_7; -- false expected
+----------+----------+----------+----------+----------+----------+----------+
| result_1 | result_2 | result_3 | result_4 | result_5 | result_6 | result_7 |
+----------+----------+----------+----------+----------+----------+----------+
| true     | true     | false    | null     | true     | true     | false    |
+----------+----------+----------+----------+----------+----------+----------+

Operator <>

Description

Checks that the left argument is not equal to the right argument.
Usage

<num> <> <num>
Aliases

!=

Synonymous with the construct NOT <num> = <num>.

Can be used not only with numeric types, but also with types for date and time.

See examples 
SELECT
    1 <> 0    AS result_1,
    1 != 0    AS result_2,
    NOT 1 = 0 AS result_3,
    1 <> NULL AS result_4,
    '2026-01-02'::DATE <> '2026-02-01'::DATE AS result_5,
    '00:00'::TIME <> '00:00:01'::TIME        AS result_6,
    '00:00'::TIME <> '12:00'::TIME           AS result_7;
+----------+----------+----------+----------+----------+----------+----------+
| result_1 | result_2 | result_3 | result_4 | result_5 | result_6 | result_7 |
+----------+----------+----------+----------+----------+----------+----------+
| true     | true     | true     | null     | true     | true     | true     |
+----------+----------+----------+----------+----------+----------+----------+

Operator BETWEEN

Description

Checks that the first argument lies in the interval between the second and third arguments inclusive.
Usage

<num> BETWEEN <num> AND <num>

Synonymous with the construct <num> <= <num> AND <num> <= <num>.

Can be used not only with numeric types, but also with date and time types.

See examples 
SELECT
    2 BETWEEN 1 AND 3    AS result_1,
    1 BETWEEN 1 AND 1    AS result_2,
    NULL BETWEEN 1 AND 1 AS result_3,
    '2026-01-02'::DATE BETWEEN '2026-01-01'::DATE AND '2026-01-03'::DATE AS result_4,
    '00:00:01'::TIME BETWEEN '00:00'::TIME AND '00:00:02'::TIME          AS result_5;
+----------+----------+----------+----------+----------+
| result_1 | result_2 | result_3 | result_4 | result_5 |
+----------+----------+----------+----------+----------+
| true     | true     | null     | true     | true     |
+----------+----------+----------+----------+----------+