# 9.3. Mathematical Functions and Operators

Mathematical operators are provided for many PostgreSQL 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 (deprecated, use ``` factorial() ``` instead) ``` 5 ! ``` ``` 120 ```
``` !! ``` factorial as a prefix operator (deprecated, use ``` factorial() ``` instead) ``` !! 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 ```
``` factorial( bigint ) ``` ``` numeric ``` factorial ``` factorial(5) ``` ``` 120 ```
``` 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 ``` equal-width 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 ``` equal-width 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

``` 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
Another way to work with angles measured in degrees is to use the unit transformation functions ``` radians() ``` and ``` degrees() ``` shown earlier. However, using the degree-based trigonometric functions is preferred, as that way avoids round-off error for special cases such as ``` sind(30) ``` .