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. A division or modulo by zero fails with a Division by zero error, SQLSTATE 22012 as in PostgreSQL. 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

Integer Overflow

Integer +, -, * and unary minus fail with an overflow error when the result does not fit the type. After SET integer_overflow = 'wrap' they keep the low bits of the result instead, the two's complement wraparound of machine arithmetic and of ClickHouse; RESET integer_overflow restores the error. Casts and division are always checked:

SQL

SELECT 9223372036854775807::BIGINT + 1 AS total;
SET integer_overflow = 'wrap';
SELECT 9223372036854775807::BIGINT + 1 AS total, 127::TINYINT * 2::TINYINT AS doubled;
RESET integer_overflow;

db error: ERROR: Overflow in addition of INT64 (9223372036854775807 + 1)!

 total                | doubled
----------------------+---------
 -9223372036854775808 |      -2

# DOCS_TEST_END

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`.
`clamp(x, min, max)`	Limits `x` to the range from `min` to `max`.
`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_char(x, format)`	Formats the number `x` as text according to the numeric template `format`, as PostgreSQL does.
`to_hex(x)`	Converts the integer `x` to lowercase hexadecimal, as PostgreSQL does.
`to_number(text, format)`	Reads a number from `text` according to the numeric template `format`, 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

`clamp(x, min, max)`

Returns min if x is smaller than min, max if x is larger than max, and x otherwise. The arguments are converted to their common type, so clamp works on any ordered type: numbers, strings, dates, intervals, lists. The result is NULL if any argument is NULL, and it is an error if min is larger than max. As in comparisons, NaN is larger than every other number.

SQL

SELECT clamp(17.4, 0, 10) AS clamp;

 clamp
-------
  10.0

`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_char(x, format)`

Formats the integer, bigint, real, double precision or numeric value x as text according to the numeric template format. A value that does not fit the digits of the template prints as # characters. An empty template returns an empty string. To format dates and times, see to_char(timestamp, format).

SQL

SELECT to_char(1234567.891, 'FM9,999,999.00') AS result;

 result
--------------
 1,234,567.89

`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

`to_number(text, format)`

Reads a numeric value from text according to the numeric template format. Characters of the template that are not patterns skip one character of the input. The result keeps the fractional digits that were read when both arguments are constants or query parameters, and as many fractional digits as the template allows otherwise; a template that is not a constant gives 3 fractional digits. EEEE is not supported for input.

SQL

SELECT to_number('12,454.8-', '99G999D9S') AS result;

 result
----------
 -12454.8

`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 Template Patterns

to_char(x, format) and to_number(text, format) read their template as PostgreSQL does. Patterns are recognized in upper and lower case, and the other characters of the template are copied to the output. The locale-dependent patterns follow the C locale: D is a period, G a comma, and L prints a space.

Pattern	Description
`9`	Digit position, dropped if insignificant.
`0`	Digit position, kept even if insignificant.
`.`	Decimal point.
`,`	Group (thousands) separator.
`D`	Decimal point of the locale.
`G`	Group separator of the locale.
`L`	Currency symbol of the locale.
`S`	Sign anchored to the number.
`MI`	Minus sign in the given position, if the number is negative.
`PL`	Plus sign in the given position, if the number is positive.
`SG`	Plus or minus sign in the given position.
`PR`	Negative value in angle brackets.
`RN`, `rn`	Roman numeral in upper or lower case, for values from 1 to 3999.
`TH`, `th`	Ordinal number suffix in upper or lower case.
`V`	Multiplies by 10 for each digit that follows, `to_char(12, 'FM99V9')` is `120`.
`EEEE`	Exponent for scientific notation, such as `4.86e-04`; only digits and a decimal point may come before it.
`FM`	Fill mode: no padding blanks and no trailing zeros.

SQL

SELECT to_char(-12.5, 'FM999.9PR') AS brackets, to_char(485, 'FMRN') AS roman, to_char(21, 'FM99th') AS ordinal, to_char(1234.5, 'FM9G999D99') AS grouped;

 brackets | roman   | ordinal | grouped
----------+---------+---------+---------
 <12.5>   | CDLXXXV | 21st    | 1,234.5

Numeric Operators​

Division and Modulo Operators​

Integer Overflow​

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

clamp(x, min, max)​

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_char(x, format)​

to_hex(x)​

to_number(text, format)​

trunc(x)​

xor(x, y)​

Numeric Template Patterns​

Numeric Operators

Division and Modulo Operators

Integer Overflow

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

`clamp(x, min, max)`

`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_char(x, format)`

`to_hex(x)`

`to_number(text, format)`

`trunc(x)`

`xor(x, y)`

Numeric Template Patterns