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
/	Float division	5 / 2	2.5
//	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 floating points division (5 / 2 = 2.5) while // performs integer division (5 // 2 = 2).

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.
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: @.

Query

SELECT abs(-17.4) AS abs;

Result

 abs------ 17.4

acos(x)

Computes the inverse cosine of x.

Query

SELECT acos(0.5) AS acos;

Result

 acos-------------------- 1.0471975511965979

acosh(x)

Computes the inverse hyperbolic cosine of x.

Query

SELECT acosh(1.5) AS acosh;

Result

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

add(x, y)

Alias for x + y.

Query

SELECT add(2, 3) AS add;

Result

 add-----   5

asin(x)

Computes the inverse sine of x.

Query

SELECT asin(0.5) AS asin;

Result

 asin-------------------- 0.5235987755982989

asinh(x)

Computes the inverse hyperbolic sine of x.

Query

SELECT asinh(0.5) AS asinh;

Result

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

atan(x)

Computes the inverse tangent of x.

Query

SELECT atan(0.5) AS atan;

Result

 atan-------------------- 0.4636476090008061

atanh(x)

Computes the inverse hyperbolic tangent of x.

Query

SELECT atanh(0.5) AS atanh;

Result

 atanh-------------------- 0.5493061443340548

atan2(y, x)

Computes the inverse tangent of (y, x).

Query

SELECT atan2(0.5, 0.5) AS atan2;

Result

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

bit_count(x)

Returns the number of bits that are set.

Query

SELECT bit_count(31) AS bit_count;

Result

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

cbrt(x)

Returns the cube root of the number.

Query

SELECT cbrt(8) AS cbrt;

Result

 cbrt------    2

ceil(x)

Rounds the number up.

Query

SELECT ceil(17.4) AS ceil;

Result

 ceil------   18

ceiling(x)

Rounds the number up. Alias of ceil.

Query

SELECT ceiling(17.4) AS ceiling;

Result

 ceiling---------      18

cos(x)

Computes the cosine of x.

Query

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

Result

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

cot(x)

Computes the cotangent of x.

Query

SELECT cot(0.5) AS cot;

Result

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

degrees(x)

Converts radians to degrees.

Query

SELECT degrees(pi()) AS degrees;

Result

 degrees---------     180

divide(x, y)

Alias for x // y.

Query

SELECT divide(5, 2) AS divide;

Result

 divide--------      2

even(x)

Round to next even number by rounding away from zero.

Query

SELECT even(2.9) AS even;

Result

 even------    4

exp(x)

Computes e ** x.

Query

SELECT exp(0.693) AS exp;

Result

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

factorial(x)

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

Query

SELECT factorial(4) AS factorial;

Result

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

fdiv(x, y)

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

Query

SELECT fdiv(5, 2) AS fdiv;

Result

 fdiv------    2

floor(x)

Rounds the number down.

Query

SELECT floor(17.4) AS floor;

Result

 floor-------    17

fmod(x, y)

Calculates the modulo value. Always returns a DOUBLE value.

Query

SELECT fmod(5, 2) AS fmod;

Result

 fmod------    1

gamma(x)

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

Query

SELECT gamma(5.5) AS gamma;

Result

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

gcd(x, y)

Computes the greatest common divisor of x and y.

Query

SELECT gcd(42, 57) AS gcd;

Result

 gcd-----   3

greatest_common_divisor(x, y)

Computes the greatest common divisor of x and y.

Query

SELECT greatest_common_divisor(42, 57) AS greatest_common_divisor;

Result

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

greatest(x1, x2, ...)

Selects the largest value.

Query

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

Result

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

isfinite(x)

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

Query

SELECT isfinite(5.5) AS isfinite;

Result

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

isinf(x)

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

Query

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

Result

 isinf------- t

isnan(x)

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

Query

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

Result

 isnan------- t

lcm(x, y)

Computes the least common multiple of x and y.

Query

SELECT lcm(42, 57) AS lcm;

Result

 lcm----- 798

least_common_multiple(x, y)

Computes the least common multiple of x and y.

Query

SELECT least_common_multiple(42, 57) AS least_common_multiple;

Result

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

least(x1, x2, ...)

Selects the smallest value.

Query

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

Result

 least-------     2

lgamma(x)

Computes the log of the gamma function.

Query

SELECT lgamma(2) AS lgamma;

Result

 lgamma--------      0

ln(x)

Computes the natural logarithm of x.

Query

SELECT ln(2) AS ln;

Result

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

log(x)

Computes the base-10 logarithm of x.

Query

SELECT log(100) AS log;

Result

 log-----   2

log10(x)

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

Query

SELECT log10(1000) AS log10;

Result

 log10-------     3

log2(x)

Computes the base-2 log of x.

Query

SELECT log2(8) AS log2;

Result

 log2------    3

multiply(x, y)

Alias for x * y.

Query

SELECT multiply(2, 3) AS multiply;

Result

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

nextafter(x, y)

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

Query

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

Result

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

pi()

Returns the value of pi.

Query

SELECT pi() AS pi;

Result

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

pow(x, y)

Computes x to the power of y.

Query

SELECT pow(2, 3) AS pow;

Result

 pow-----   8

power(x, y)

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

Query

SELECT power(2, 3) AS power;

Result

 power-------     8

radians(x)

Converts degrees to radians.

Query

SELECT radians(90) AS radians;

Result

 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.

Query

SELECT round_even(24.5, 0) AS round_even;

Result

 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.

Query

SELECT roundbankers(24.5, 0) AS roundbankers;

Result

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

round(v NUMERIC, s INTEGER)

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

Query

SELECT round(42.4332, 2) AS round;

Result

 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.

Query

SELECT sign(-349) AS sign;

Result

 sign------   -1

signbit(x)

Returns whether the signbit is set or not.

Query

SELECT signbit(-1.0) AS signbit;

Result

 signbit--------- t

sin(x)

Computes the sin of x.

Query

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

Result

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

sqrt(x)

Returns the square root of the number.

Query

SELECT sqrt(9) AS sqrt;

Result

 sqrt------    3

subtract(x, y)

Alias for x - y.

Query

SELECT subtract(2, 3) AS subtract;

Result

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

tan(x)

Computes the tangent of x.

Query

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

Result

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

trunc(x)

Truncates the number.

Query

SELECT trunc(17.4) AS trunc;

Result

 trunc-------    17

xor(x, y)

Bitwise XOR.

Query

SELECT xor(17, 5) AS xor;

Result

 xor-----  20