13.5. Mathematical Functions and Operators

Mathematical Operators

Operator Description
+ Addition
- Subtraction
* Multiplication
/ Division (integer division performs truncation)
% Modulus (remainder)

Mathematical Functions

abs(x) → [same as input]

Returns the absolute value of x.

cbrt(x) → double

Returns the cube root of x.

ceil(x) → [same as input]

This is an alias for ceiling().

ceiling(x) → [same as input]

Returns x rounded up to the nearest integer.

cosine_similarity(x, y) → double

Returns the cosine similarity between the sparse vectors x and y:

SELECT cosine_similarity(MAP(ARRAY['a'], ARRAY[1.0]), MAP(ARRAY['a'], ARRAY[2.0])); -- 1.0
degrees(x) → double

Converts angle x in radians to degrees.

e() → double

Returns the constant Euler’s number.

exp(x) → double

Returns Euler’s number raised to the power of x.

floor(x) → [same as input]

Returns x rounded down to the nearest integer.

from_base(string, radix) → bigint

Returns the value of string interpreted as a base-radix number.

inverse_normal_cdf(mean, sd, p) → double

Compute the inverse of the Normal cdf with given mean and standard deviation (sd) for the cumulative probability (p): P(N < n). The mean must be a real value and the standard deviation must be a real and positive value. The probability p must lie on the interval (0, 1).

normal_cdf(mean, sd, v) → double

Compute the Normal cdf with given mean and standard deviation (sd): P(N < v; mean, sd). The mean and value v must be real values and the standard deviation must be a real and positive value.

ln(x) → double

Returns the natural logarithm of x.

log2(x) → double

Returns the base 2 logarithm of x.

log10(x) → double

Returns the base 10 logarithm of x.

log(x, b) → double

Returns the base b logarithm of x.

mod(n, m) → [same as input]

Returns the modulus (remainder) of n divided by m.

pi() → double

Returns the constant Pi.

pow(x, p) → double

This is an alias for power().

power(x, p) → double

Returns x raised to the power of p.

radians(x) → double

Converts angle x in degrees to radians.

rand() → double

This is an alias for random().

random() → double

Returns a pseudo-random value in the range 0.0 <= x < 1.0.

random(n) → [same as input]

Returns a pseudo-random number between 0 and n (exclusive).

round(x) → [same as input]

Returns x rounded to the nearest integer.

round(x, d) → [same as input]

Returns x rounded to d decimal places.

sign(x) → [same as input]

Returns the signum function of x, that is:

  • 0 if the argument is 0,
  • 1 if the argument is greater than 0,
  • -1 if the argument is less than 0.

For double arguments, the function additionally returns:

  • NaN if tha argument is NaN,
  • 1 if the argument is +Infinity,
  • -1 if the argument is -Infinity.
sqrt(x) → double

Returns the square root of x.

to_base(x, radix) → varchar

Returns the base-radix representation of x.

truncate(x) → double

Returns x rounded to integer by dropping digits after decimal point.

width_bucket(x, bound1, bound2, n) → bigint

Returns the bin number of x in an equi-width histogram with the specified bound1 and bound2 bounds and n number of buckets.

width_bucket(x, bins) → bigint

Returns the bin number of x according to the bins specified by the array bins. The bins parameter must be an array of doubles and is assumed to be in sorted ascending order.

Trigonometric Functions

All trigonometric function arguments are expressed in radians. See unit conversion functions degrees() and radians().

acos(x) → double

Returns the arc cosine of x.

asin(x) → double

Returns the arc sine of x.

atan(x) → double

Returns the arc tangent of x.

atan2(y, x) → double

Returns the arc tangent of y / x.

cos(x) → double

Returns the cosine of x.

cosh(x) → double

Returns the hyperbolic cosine of x.

sin(x) → double

Returns the sine of x.

tan(x) → double

Returns the tangent of x.

tanh(x) → double

Returns the hyperbolic tangent of x.

Floating Point Functions

infinity() → double

Returns the constant representing positive infinity.

is_finite(x) → boolean

Determine if x is finite.

is_infinite(x) → boolean

Determine if x is infinite.

is_nan(x) → boolean

Determine if x is not-a-number.

nan() → double

Returns the constant representing not-a-number.