Mathematical Functions and Operators

ClapDB query SQL will fully compatibility with PostgreSQL’s SQL, and mathematical functions and operators’ current state can be checked in below chart.

according to PostgreSQL Mathematical Functions and Operators

Mathematical Operator

PostgreSQL Mathematical Operator	Description	Formular	Example
’+‘	Additional	numeric_type + numeric_type → numeric_type	2 + 3 → 5
’+‘	Unary plus (no operation)	+ numeric_type → numeric_type	+ 3.5 → 3.5
’-‘	Subtraction	numeric_type - numeric_type → numeric_type	2 - 3 → -1
’-‘	Negation	- numeric_type → numeric_type	- 3.5 → 3.5
’*‘	Multiplication	numeric_type * numeric_type → numeric_type	2 * 3 → 6
/	Division (for integral types, division truncates the result towards zero)	numeric_type / numeric_type → numeric_type	3 / 2 → 1
%	Modulo (remainder); available for smallint, integer, bigint, and numeric	numeric_type % numeric_type → numeric_type	5 % 4 → 1
^	Exponentiation	double precision ^ double precision → double precision	2 ^ 3 → 8
\|/	Square root	\|/ double precision → double precision	\|/ 25 → 5
\|\|/	Cube root	\|\|/ double precision → double precision	\|\|/ 27 → 3
@	Absolute value	@numeric_type → numeric_type	@ -4 → 4
&	Bitwise AND	integeral_type & integeral_type → integeral_type	19 & 15 → 11
\|	Bitwise OR	integeral_type \| integeral_type → integeral_type	32 \| 3 → 35
#	Bitwise exclusive OR	integeral_type # integeral_type → integeral_type	17 # 5 → 20
~	Bitwise NOT	~ integeral_type → integeral_type	~1 → 2
<<	Bitwise shift left	integer << integer → integer	8 << 2 → 32
>>	Bitwise shift right	integer >> integer → integer	8 >> 2 → 2

Mathematical Function

PostgreSQL Mathematical Function	Description	Formular	Example
abs	Absolute	abs(numeric_type) → numeric_type	abs(-17.4) → 17.4
cbrt	Cube root	cbrt(double precision) → double precision	cbrt(27) → 3
ceil	Nearest integer greater than or equal to argument	ceil(numeric_type) → numeric_type	ceil(42.2) → 43
ceil		cel(double precision) -> double precision	ceil(-42.2) -> -42
ceiling	Nearest integer greater than or equal to argument	ceiling(numeric_type) → numeric_type	ceiling(42.2) → 43
ceiling		cel(double precision) -> double precision	ceiling(-42.2) -> -42
degrees	Converts radians to degrees	degrees(double precision) → double precision	degrees(0.5) → 28.6478897565412
div	Integer quotient of y/x (truncates towards zero)	div(numeric, numeric) → numeric	div(9, 4) → 2
erf	Error function	erf(double precision) → double precision	erf(0.5) → 0.520499877813047
erfc	Complementary error function (1 - erf(x), without loss of precision for large inputs)	erfc(double precision) → double precision	erfc(0.5) → 0.479500122186953
exp	Exponential function	exp(numeric) → numeric	exp(1) → 2.71828182845905
exp		exp(double precision) → double precision	exp(1) → 2.71828182845905
factorial	Factorial	factorial(bigint) → numeric	factorial(5) → 120
floor	Nearest integer less than or equal to argument	floor(numeric_type) → numeric_type	floor(42.8) → 42
floor		floor(double precision) -> double precision	floor(-42.8) -> -43
gcd	Greatest common divisor	gcd(integer, integer) → integer	gcd(1071, 462) → 21
lcm	Least common multiple (the smallest strictly positive number that is an integral multiple of both inputs); returns 0 if either input is zero;	lcm(numeric_type, numeric_type) → numeric_type	lcm(1071, 462) → 23562
ln	Natural logarithm	ln(numeric) → numeric	ln(2.0) → 0.6931471805599453
ln		ln(double precision) → double precision	ln(2.0) → 0.6931471805599453
log	Base 10 logarithm	log(numeric) → numeric	log(100.0) → 2
log		log(double precision) → double precision	log(100.0) → 2
log10	Base 10 logarithm	log10(numeric) → numeric	log10(100.0) → 2
log10		log10(double precision) → double precision	log10(100.0) → 2
log	Logarithm of x to base b	log(numeric, numeric) → numeric	log(2.0, 64.0) → 6.0
min_scale	Minimum scale (number of fractional decimal digits) needed to represent the supplied value precisely	min_scale(numeric) → integer	min_scale(1.23) → 2
mod	Remainder of y/x; available for smallint, integer, bigint, and numeric	mod(numeric, numeric) → numeric	mod(5, 3) → 2
pi	Approximate value of π	pi() → double precision	pi() → 3.141592653589793
power	a raised to the power of b	power(numeric, numeric) → numeric	power(2, 3) → 8
power		power(double precision, double precision) → double precision	power(2.0, 3.0) → 8.0
radians	Converts degrees to radians	radians(double precision) → double precision	radians(45.0) → 0.785398163397448
round	Rounds to nearest integer. For numeric, ties are broken by rounding away from zero. For double precision, the tie-breaking behavior is platform dependent, but “round to nearest even” is the most common rule.	round(numeric_type) → numeric_type	round(42.4) → 42
round		round(double precision) -> double precision	round(-42.4) -> -42
round	Rounds v to s decimal places. Ties are broken by rounding away from zero.	round(numeric_type, integer) → numeric_type	round(42.4382, 2) → 42.44
scale	Scale of the argument (the number of decimal digits in the fractional part)	scale(numeric) → integer	scale(8.4100) → 4
sign	Sign of the argument (-1, 0, or +1)	sign(numeric_type) → integer	sign(-42.8) → -1
sign		sign(double precision) -> integer	sign(-42.8) -> -1
sqrt	Square root	sqrt(numeric) → numeric	sqrt(25) → 5
sqrt		sqrt(double precision) → double precision	sqrt(25) → 5
trim_scale	Reduces the value’s scale (number of fractional decimal digits) by removing trailing zeroes	trim_scale(numeric) → numeric	trim_scale(1.230) → 1.23
trunc	Truncates to integer (towards zero)	trunc(numeric_type) → numeric_type	trunc(42.8) → 42
trunc		trunc(double precision) -> double precision	trunc(-42.8) -> -42
trunc	Truncates v to s decimal places	trunc(numeric_type, integer) → numeric_type	trunc(42.4382, 2) → 42.43
width_bucket	Returns the number of the bucket in which operand falls in a histogram having count equal-width buckets spanning the range low to high. Returns 0 or count+1 for an input outside that range.	width_bucket(operand numeric, low numeric, high numeric, count integer) → integer
width_bucket		width_bucket(operand double precision, low double precision, high double precision, count integer) → integer	width_bucket(5.35, 0.024, 10.06, 5) → 3
width_bucket	Returns the number of the bucket in which operand falls given an array listing the lower bounds of the buckets. Returns 0 for an input less than the first lower bound. operand and the array elements can be of any type having standard comparison operators. The thresholds array must be sorted, smallest first, or unexpected results will be obtained.	width_bucket(operand anycompatible, thresholds anycompatiblearray) → integer	width_bucket(now(), array[‘yesterday’, ‘today’, ‘tomorrow’]::timestamptz[]) → 2

Randome Function

PostgreSQL Mathematical Function	Description	Formular	Example
random	Returns a random value in the range 0.0 < x < 1.0	random() → double precision	random() → 0.298594
random_normal	Returns a random value from the normal distribution with the given parameters; mean defaults to 0.0 and stddev defaults to 1.0	random_normal(mean double precision, ) → double precision	random_normal() → 0.298594
set_seed	Sets the seed for the random number generator	set_seed(seed double precision) → void	set_seed(0.5)

Trigonometric Functions

not supported by ClapDB by now

Hyperbolic Functions

not supported by ClapDB by now