Numeric Functions

Numeric Operators

The table below shows the available mathematical operators for numeric types.

Operator	Description	Example	Result
`+`	Addition	`2 + 3`	`5`
`-`	Subtraction	`2 - 3`	`-1`
`*`	Multiplication	`2 * 3`	`6`
`/`	Division	`5 / 2`	`2`
`//`	Division	`5 // 2`	`2`
`%`	Modulo (remainder)	`5 % 4`	`1`
`**`	Exponent	`3 ** 4`	`81`
`^`	Exponent (alias for `**`)	`3 ^ 4`	`81`
`&`	Bitwise AND	`91 & 15`	`11`
`\|`	Bitwise OR	`32 \| 3`	`35`
`<<`	Bitwise shift left	`1 << 4`	`16`
`>>`	Bitwise shift right	`8 >> 2`	`2`
`~`	Bitwise negation	`~15`	`-16`
`!`	Factorial of `x`	`4!`	`24`

Division and Modulo Operators

There are two division operators: / and //. They are equivalent when at least one of the operands is a FLOAT or a DOUBLE. When both operands are integers, // performs integer division (5 // 2 = 2) and / follows the integer_division setting: a server truncates like PostgreSQL (5 / 2 = 2), while serened shell divides as floats (5 / 2 = 2.5). Write 5 / 2.0 for a fractional result on both. Integer division or modulo by zero fails with a Division by zero error. To return NULL instead, wrap the expression in TRY(...), or run SET error_on_division_by_zero = false to return NULL for all divisions by zero:

SQL

SELECT 1 // 0 AS quotient;
SELECT TRY(1 // 0) AS quotient, TRY(5 % 0) AS remainder;
SELECT 5 % 0 AS remainder;
SET error_on_division_by_zero = false;
SELECT 1 // 0 AS quotient, 5 % 0 AS remainder;
RESET error_on_division_by_zero;

db error: ERROR: Division by zero in expression (1 // 0). Use TRY(...) to return NULL for this expression, or SET error_on_division_by_zero=false to return NULL for all divisions by zero.

 quotient | remainder
----------+-----------
 NULL     | NULL

# DOCS_TEST_END

error Division by zero

 quotient | remainder
----------+-----------
 NULL     | NULL

Supported Types

The modulo, bitwise, negation, and factorial operators work only on integral data types, whereas the others are available for all numeric data types.

Numeric Functions

The table below shows the available mathematical functions.

Name	Description
`@(x)`	Absolute value. Parentheses are optional if `x` is a column name.
`abs(x)`	Absolute value.
`acos(x)`	Computes the inverse cosine of `x`.
`acosh(x)`	Computes the inverse hyperbolic cosine of `x`.
`add(x, y)`	Alias for `x + y`.
`asin(x)`	Computes the inverse sine of `x`.
`asinh(x)`	Computes the inverse hyperbolic sine of `x`.
`atan(x)`	Computes the inverse tangent of `x`.
`atanh(x)`	Computes the inverse hyperbolic tangent of `x`.
`atan2(y, x)`	Computes the inverse tangent of `(y, x)`.
`bit_count(x)`	Returns the number of bits that are set.
`cbrt(x)`	Returns the cube root of the number.
`ceil(x)`	Rounds the number up.
`ceiling(x)`	Rounds the number up. Alias of `ceil`.
`cos(x)`	Computes the cosine of `x`.
`cot(x)`	Computes the cotangent of `x`.
`degrees(x)`	Converts radians to degrees.
`divide(x, y)`	Alias for `x // y`.
`even(x)`	Round to next even number by rounding away from zero.
`exp(x)`	Computes `e ** x`.
`factorial(x)`	See the `!` operator. Computes the product of the current integer and all integers below it.
`fdiv(x, y)`	Performs integer division (`x // y`) but returns a `DOUBLE` value.
`floor(x)`	Rounds the number down.
`fmod(x, y)`	Calculates the modulo value. Always returns a `DOUBLE` value.
`gamma(x)`	Interpolation of the factorial of `x - 1`. Fractional inputs are allowed.
`gcd(x, y)`	Computes the greatest common divisor of `x` and `y`.
`greatest_common_divisor(x, y)`	Computes the greatest common divisor of `x` and `y`.
`greatest(x1, x2, ...)`	Selects the largest value.
`isfinite(x)`	Returns true if the floating point value is finite, false otherwise.
`isinf(x)`	Returns true if the floating point value is infinite, false otherwise.
`isnan(x)`	Returns true if the floating point value is not a number, false otherwise.
`lcm(x, y)`	Computes the least common multiple of `x` and `y`.
`least_common_multiple(x, y)`	Computes the least common multiple of `x` and `y`.
`least(x1, x2, ...)`	Selects the smallest value.
`lgamma(x)`	Computes the log of the `gamma` function.
`ln(x)`	Computes the natural logarithm of `x`.
`log(x)`	Computes the base-10 logarithm of `x`.
`log10(x)`	Alias of `log`. Computes the base-10 logarithm of `x`.
`log2(x)`	Computes the base-2 log of `x`.
`multiply(x, y)`	Alias for `x * y`.
`nextafter(x, y)`	Return the next floating point value after `x` in the direction of `y`.
`pi()`	Returns the value of pi.
`pow(x, y)`	Computes `x` to the power of `y`.
`power(x, y)`	Alias of `pow`. Computes `x` to the power of `y`.
`radians(x)`	Converts degrees to radians.
`random()`	Returns a random number `x` in the range `0.0 <= x < 1.0`.
`round_even(v NUMERIC, s INTEGER)`	Alias of `roundbankers(v, s)`. Round to `s` decimal places using the rounding half to even rule. Values `s < 0` are allowed.
`roundbankers(v NUMERIC, s INTEGER)`	Alias of `round_even(v, s)`. Round to `s` decimal places using the rounding half to even rule. Values `s < 0` are allowed.
`round(v NUMERIC, s INTEGER)`	Round to `s` decimal places. Values `s < 0` are allowed.
`setseed(x)`	Sets the seed to be used for the random function.
`sign(x)`	Returns the sign of `x` as -1, 0 or 1.
`signbit(x)`	Returns whether the signbit is set or not.
`sin(x)`	Computes the sin of `x`.
`sqrt(x)`	Returns the square root of the number.
`subtract(x, y)`	Alias for `x - y`.
`tan(x)`	Computes the tangent of `x`.
`to_hex(x)`	Converts the integer `x` to lowercase hexadecimal, as PostgreSQL does.
`trunc(x)`	Truncates the number.
`xor(x, y)`	Bitwise XOR.

`@(x)`

Description	Absolute value. Parentheses are optional if `x` is a column name.
Example	`@(-17.4)`
Result	`17.4`
Alias	`abs`

`abs(x)`

Absolute value. Alias: @.

SQL

SELECT abs(-17.4) AS abs;

 abs
------
 17.4

`acos(x)`

Computes the inverse cosine of x.

SQL

SELECT acos(0.5) AS acos;

 acos
---------------------------
 1.047197551196597(varies)

`acosh(x)`

Computes the inverse hyperbolic cosine of x.

SQL

SELECT acosh(1.5) AS acosh;

 acosh
--------------------
 0.9624236501192069

`add(x, y)`

Alias for x + y.

SQL

SELECT add(2, 3) AS add;

 add
-----
   5

`asin(x)`

Computes the inverse sine of x.

SQL

SELECT asin(0.5) AS asin;

 asin
---------------------------
 0.523598775598298(varies)

`asinh(x)`

Computes the inverse hyperbolic sine of x.

SQL

SELECT asinh(0.5) AS asinh;

 asinh
---------------------
 0.48121182505960347

`atan(x)`

Computes the inverse tangent of x.

SQL

SELECT atan(0.5) AS atan;

 atan
---------------------------
 0.463647609000806(varies)

`atanh(x)`

Computes the inverse hyperbolic tangent of x.

SQL

SELECT atanh(0.5) AS atanh;

 atanh
---------------------------
 0.549306144334054(varies)

`atan2(y, x)`

Computes the inverse tangent of (y, x).

SQL

SELECT atan2(0.5, 0.5) AS atan2;

 atan2
--------------------
 0.7853981633974483

`bit_count(x)`

Returns the number of bits that are set.

SQL

SELECT bit_count(31) AS bit_count;

 bit_count
-----------
         5

`cbrt(x)`

Returns the cube root of the number.

SQL

SELECT cbrt(8) AS cbrt;

 cbrt
------
    2

`ceil(x)`

Rounds the number up.

SQL

SELECT ceil(17.4) AS ceil;

 ceil
------
   18

`ceiling(x)`

Rounds the number up. Alias of ceil.

SQL

SELECT ceiling(17.4) AS ceiling;

 ceiling
---------
      18

`cos(x)`

Computes the cosine of x.

SQL

SELECT cos(pi() / 3) AS cos;

 cos
--------------------
 0.5000000000000001

`cot(x)`

Computes the cotangent of x.

SQL

SELECT cot(0.5) AS cot;

 cot
-------------------
 1.830487721712452

`degrees(x)`

Converts radians to degrees.

SQL

SELECT degrees(pi()) AS degrees;

 degrees
---------
     180

`divide(x, y)`

Alias for x // y.

SQL

SELECT divide(5, 2) AS divide;

 divide
--------
      2

`even(x)`

Round to next even number by rounding away from zero.

SQL

SELECT even(2.9) AS even;

 even
------
    4

`exp(x)`

Computes e ** x.

SQL

SELECT exp(0.693) AS exp;

 exp
--------------------
 1.9997056605411638

`factorial(x)`

See the ! operator. Computes the product of the current integer and all integers below it.

SQL

SELECT factorial(4) AS factorial;

 factorial
-----------
        24

`fdiv(x, y)`

Performs integer division (x // y) but returns a DOUBLE value.

SQL

SELECT fdiv(5, 2) AS fdiv;

 fdiv
------
    2

`floor(x)`

Rounds the number down.

SQL

SELECT floor(17.4) AS floor;

 floor
-------
    17

`fmod(x, y)`

Calculates the modulo value. Always returns a DOUBLE value.

SQL

SELECT fmod(5, 2) AS fmod;

 fmod
------
    1

`gamma(x)`

Interpolation of the factorial of x - 1. Fractional inputs are allowed.

SQL

SELECT gamma(5.5) AS gamma;

 gamma
-------------------
 52.34277778455352

`gcd(x, y)`

Computes the greatest common divisor of x and y.

SQL

SELECT gcd(42, 57) AS gcd;

 gcd
-----
   3

`greatest_common_divisor(x, y)`

Computes the greatest common divisor of x and y.

SQL

SELECT greatest_common_divisor(42, 57) AS greatest_common_divisor;

 greatest_common_divisor
-------------------------
                       3

`greatest(x1, x2, ...)`

Selects the largest value.

SQL

SELECT greatest(3, 2, 4, 4) AS greatest;

 greatest
----------
        4

`isfinite(x)`

Returns true if the floating point value is finite, false otherwise.

SQL

SELECT isfinite(5.5) AS isfinite;

 isfinite
----------
 t

`isinf(x)`

Returns true if the floating point value is infinite, false otherwise.

SQL

SELECT isinf('Infinity'::FLOAT) AS isinf;

 isinf
-------
 t

`isnan(x)`

Returns true if the floating point value is not a number, false otherwise.

SQL

SELECT isnan('NaN'::FLOAT) AS isnan;

 isnan
-------
 t

`lcm(x, y)`

Computes the least common multiple of x and y.

SQL

SELECT lcm(42, 57) AS lcm;

 lcm
-----
 798

`least_common_multiple(x, y)`

Computes the least common multiple of x and y.

SQL

SELECT least_common_multiple(42, 57) AS least_common_multiple;

 least_common_multiple
-----------------------
                   798

`least(x1, x2, ...)`

Selects the smallest value.

SQL

SELECT least(3, 2, 4, 4) AS least;

 least
-------
     2

`lgamma(x)`

Computes the log of the gamma function.

SQL

SELECT lgamma(2) AS lgamma;

 lgamma
--------
      0

`ln(x)`

Computes the natural logarithm of x.

SQL

SELECT ln(2) AS ln;

 ln
--------------------
 0.6931471805599453

`log(x)`

Computes the base-10 logarithm of x.

SQL

SELECT log(100) AS log;

 log
-----
   2

`log10(x)`

Alias of log. Computes the base-10 logarithm of x.

SQL

SELECT log10(1000) AS log10;

 log10
-------
     3

`log2(x)`

Computes the base-2 log of x.

SQL

SELECT log2(8) AS log2;

 log2
------
    3

`multiply(x, y)`

Alias for x * y.

SQL

SELECT multiply(2, 3) AS multiply;

 multiply
----------
        6

`nextafter(x, y)`

Return the next floating point value after x in the direction of y.

SQL

SELECT nextafter(1::FLOAT, 2::FLOAT) AS nextafter;

 nextafter
-----------
 1.0000001

`pi()`

Returns the value of pi.

SQL

SELECT pi() AS pi;

 pi
-------------------
 3.141592653589793

`pow(x, y)`

Computes x to the power of y.

SQL

SELECT pow(2, 3) AS pow;

 pow
-----
   8

`power(x, y)`

Alias of pow. Computes x to the power of y.

SQL

SELECT power(2, 3) AS power;

 power
-------
     8

`radians(x)`

Converts degrees to radians.

SQL

SELECT radians(90) AS radians;

 radians
--------------------
 1.5707963267948966

`random()`

Description	Returns a random number `x` in the range `0.0 <= x < 1.0`.
Example	`random()`
Result	various

`round_even(v NUMERIC, s INTEGER)`

Alias of roundbankers(v, s). Round to s decimal places using the rounding half to even rule. Values s < 0 are allowed.

SQL

SELECT round_even(24.5, 0) AS round_even;

 round_even
------------
         24

`roundbankers(v NUMERIC, s INTEGER)`

Alias of round_even(v, s). Round to s decimal places using the rounding half to even rule. Values s < 0 are allowed.

SQL

SELECT roundbankers(24.5, 0) AS roundbankers;

 roundbankers
--------------
           24

`round(v NUMERIC, s INTEGER)`

Round to s decimal places. Values s < 0 are allowed.

SQL

SELECT round(42.4332, 2) AS round;

 round
-------
 42.43

`setseed(x)`

Description	Sets the seed to be used for the random function.
Example	`setseed(0.42)`

`sign(x)`

Returns the sign of x as -1, 0 or 1.

SQL

SELECT sign(-349) AS sign;

 sign
------
   -1

`signbit(x)`

Returns whether the signbit is set or not.

SQL

SELECT signbit(-1.0) AS signbit;

 signbit
---------
 t

`sin(x)`

Computes the sin of x.

SQL

SELECT sin(pi() / 6) AS sin;

 sin
---------------------
 0.49999999999999994

`sqrt(x)`

Returns the square root of the number.

SQL

SELECT sqrt(9) AS sqrt;

 sqrt
------
    3

`subtract(x, y)`

Alias for x - y.

SQL

SELECT subtract(2, 3) AS subtract;

 subtract
----------
       -1

`tan(x)`

Computes the tangent of x.

SQL

SELECT tan(pi() / 4) AS tan;

 tan
--------------------
 0.9999999999999999

`to_hex(x)`

Converts the integer or bigint x to lowercase hexadecimal, as PostgreSQL does. A negative value is shown in the two's complement of its own width: to_hex(-1) is ffffffff, to_hex(-1::BIGINT) is ffffffffffffffff. For a string or blob, to_hex is an alias of hex.

SQL

SELECT to_hex(-1) AS to_hex;

 to_hex
----------
 ffffffff

`trunc(x)`

Truncates the number.

SQL

SELECT trunc(17.4) AS trunc;

 trunc
-------
    17

`xor(x, y)`

Bitwise XOR.

SQL

SELECT xor(17, 5) AS xor;

 xor
-----
  20

Numeric Operators​

Division and Modulo Operators​

Supported Types​

Numeric Functions​

@(x)​

abs(x)​

acos(x)​

acosh(x)​

add(x, y)​

asin(x)​

asinh(x)​

atan(x)​

atanh(x)​

atan2(y, x)​

bit_count(x)​

cbrt(x)​

ceil(x)​

ceiling(x)​

cos(x)​

cot(x)​

degrees(x)​

divide(x, y)​

even(x)​

exp(x)​

factorial(x)​

fdiv(x, y)​

floor(x)​

fmod(x, y)​

gamma(x)​

gcd(x, y)​

greatest_common_divisor(x, y)​

greatest(x1, x2, ...)​

isfinite(x)​

isinf(x)​

isnan(x)​

lcm(x, y)​

least_common_multiple(x, y)​

least(x1, x2, ...)​

lgamma(x)​

ln(x)​

log(x)​

log10(x)​

log2(x)​

multiply(x, y)​

nextafter(x, y)​

pi()​

pow(x, y)​

power(x, y)​

radians(x)​

random()​

round_even(v NUMERIC, s INTEGER)​

roundbankers(v NUMERIC, s INTEGER)​

round(v NUMERIC, s INTEGER)​

setseed(x)​

sign(x)​

signbit(x)​

sin(x)​

sqrt(x)​

subtract(x, y)​

tan(x)​

to_hex(x)​

trunc(x)​

xor(x, y)​

Numeric Operators

Division and Modulo Operators

Supported Types

Numeric Functions

`@(x)`

`abs(x)`

`acos(x)`

`acosh(x)`

`add(x, y)`

`asin(x)`

`asinh(x)`

`atan(x)`

`atanh(x)`

`atan2(y, x)`

`bit_count(x)`

`cbrt(x)`

`ceil(x)`

`ceiling(x)`

`cos(x)`

`cot(x)`

`degrees(x)`

`divide(x, y)`

`even(x)`

`exp(x)`

`factorial(x)`

`fdiv(x, y)`

`floor(x)`

`fmod(x, y)`

`gamma(x)`

`gcd(x, y)`

`greatest_common_divisor(x, y)`

`greatest(x1, x2, ...)`

`isfinite(x)`

`isinf(x)`

`isnan(x)`

`lcm(x, y)`

`least_common_multiple(x, y)`

`least(x1, x2, ...)`

`lgamma(x)`

`ln(x)`

`log(x)`

`log10(x)`

`log2(x)`

`multiply(x, y)`

`nextafter(x, y)`

`pi()`

`pow(x, y)`

`power(x, y)`

`radians(x)`

`random()`

`round_even(v NUMERIC, s INTEGER)`

`roundbankers(v NUMERIC, s INTEGER)`

`round(v NUMERIC, s INTEGER)`

`setseed(x)`

`sign(x)`

`signbit(x)`

`sin(x)`

`sqrt(x)`

`subtract(x, y)`

`tan(x)`

`to_hex(x)`

`trunc(x)`

`xor(x, y)`