9.3. Mathematical Functions and Operators
Mathematical operators are provided for many Postgres Pro types. For types without standard mathematical conventions (e.g., date/time types) we describe the actual behavior in subsequent sections.
Table 9.4 shows the available mathematical operators.
Table 9.4. Mathematical Operators
Operator  Description  Example  Result 

+  addition  2 + 3  5 
  subtraction  2  3  1 
*  multiplication  2 * 3  6 
/  division (integer division truncates the result)  4 / 2  2 
%  modulo (remainder)  5 % 4  1 
^  exponentiation (associates left to right)  2.0 ^ 3.0  8 
/  square root  / 25.0  5 
/  cube root  / 27.0  3 
!  factorial  5 !  120 
!!  factorial (prefix operator)  !! 5  120 
@  absolute value  @ 5.0  5 
&  bitwise AND  91 & 15  11 
  bitwise OR  32  3  35 
#  bitwise XOR  17 # 5  20 
~  bitwise NOT  ~1  2 
<<  bitwise shift left  1 << 4  16 
>>  bitwise shift right  8 >> 2  2 
The bitwise operators work only on integral data types and are also available for the bit string types bit
and bit varying
, as shown in Table 9.13.
Table 9.5 shows the available mathematical functions. In the table, dp
indicates double precision
. Many of these functions are provided in multiple forms with different argument types. Except where noted, any given form of a function returns the same data type as its argument. The functions working with double precision
data are mostly implemented on top of the host system's C library; accuracy and behavior in boundary cases can therefore vary depending on the host system.
Table 9.5. Mathematical Functions
Function  Return Type  Description  Example  Result 

abs(x )
 (same as input)  absolute value  abs(17.4)  17.4 
cbrt(dp )
 dp  cube root  cbrt(27.0)  3 
ceil(dp or numeric )
 (same as input)  nearest integer greater than or equal to argument  ceil(42.8)  42 
ceiling(dp or numeric )
 (same as input)  nearest integer greater than or equal to argument (same as ceil )  ceiling(95.3)  95 
degrees(dp )
 dp  radians to degrees  degrees(0.5)  28.6478897565412 
div(y numeric , x numeric )
 numeric  integer quotient of y /x  div(9,4)  2 
exp(dp or numeric )
 (same as input)  exponential  exp(1.0)  2.71828182845905 
floor(dp or numeric )
 (same as input)  nearest integer less than or equal to argument  floor(42.8)  43 
ln(dp or numeric )
 (same as input)  natural logarithm  ln(2.0)  0.693147180559945 
log(dp or numeric )
 (same as input)  base 10 logarithm  log(100.0)  2 
log(b numeric , x numeric )
 numeric  logarithm to base b  log(2.0, 64.0)  6.0000000000 
mod(y , x )
 (same as argument types)  remainder of y /x  mod(9,4)  1 
pi()
 dp  “π” constant  pi()  3.14159265358979 
power(a dp , b dp )
 dp  a raised to the power of b  power(9.0, 3.0)  729 
power(a numeric , b numeric )
 numeric  a raised to the power of b  power(9.0, 3.0)  729 
radians(dp )
 dp  degrees to radians  radians(45.0)  0.785398163397448 
round(dp or numeric )
 (same as input)  round to nearest integer  round(42.4)  42 
round(v numeric , s int )
 numeric  round to s decimal places  round(42.4382, 2)  42.44 
scale(numeric )
 integer  scale of the argument (the number of decimal digits in the fractional part)  scale(8.41)  2 
sign(dp or numeric )
 (same as input)  sign of the argument (1, 0, +1)  sign(8.4)  1 
sqrt(dp or numeric )
 (same as input)  square root  sqrt(2.0)  1.4142135623731 
trunc(dp or numeric )
 (same as input)  truncate toward zero  trunc(42.8)  42 
trunc(v numeric , s int )
 numeric  truncate to s decimal places  trunc(42.4382, 2)  42.43 
width_bucket(operand dp , b1 dp , b2 dp , count int )
 int  return the bucket number to which operand would be assigned in a histogram having count equalwidth buckets spanning the range b1 to b2 ; returns 0 or count +1 for an input outside the range  width_bucket(5.35, 0.024, 10.06, 5)  3 
width_bucket(operand numeric , b1 numeric , b2 numeric , count int )
 int  return the bucket number to which operand would be assigned in a histogram having count equalwidth buckets spanning the range b1 to b2 ; returns 0 or count +1 for an input outside the range  width_bucket(5.35, 0.024, 10.06, 5)  3 
width_bucket(operand anyelement , thresholds anyarray )
 int  return the bucket number to which operand would be assigned given an array listing the lower bounds of the buckets; returns 0 for an input less than the first lower bound; the thresholds array must be sorted, smallest first, or unexpected results will be obtained  width_bucket(now(), array['yesterday', 'today', 'tomorrow']::timestamptz[])  2 
Table 9.6 shows functions for generating random numbers.
Table 9.6. Random Functions
Function  Return Type  Description 

random()
 dp  random value in the range 0.0 <= x < 1.0 
setseed(dp )
 void  set seed for subsequent random() calls (value between 1.0 and 1.0, inclusive) 
The characteristics of the values returned by random()
depend on the system implementation. It is not suitable for cryptographic applications; see pgcrypto module for an alternative.
Finally, Table 9.7 shows the available trigonometric functions. All trigonometric functions take arguments and return values of type double precision
. Each of the trigonometric functions comes in two variants, one that measures angles in radians and one that measures angles in degrees.
Table 9.7. Trigonometric Functions
Function (radians)  Function (degrees)  Description 

acos(x )
 acosd(x )
 inverse cosine 
asin(x )
 asind(x )
 inverse sine 
atan(x )
 atand(x )
 inverse tangent 
atan2(y , x )
 atan2d(y , x )
 inverse tangent of y /x

cos(x )
 cosd(x )
 cosine 
cot(x )
 cotd(x )
 cotangent 
sin(x )
 sind(x )
 sine 
tan(x )
 tand(x )
 tangent 
Note
Another way to work with angles measured in degrees is to use the unit transformation functions radians()
and degrees()
shown earlier. However, using the degreebased trigonometric functions is preferred, as that way avoids roundoff error for special cases such as sind(30)
.