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
Table 9.6 shows functions for generating random numbers.
Table 9.6. Random Functions
The characteristics of the values returned by
depend
on the system implementation. It is not suitable for cryptographic
applications; see
pgcrypto
module for an alternative.
random()
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
Note
Another way to work with angles measured in degrees is to use the unit
transformation functions
and
radians()
shown earlier.
However, using the degreebased trigonometric functions is preferred,
as that way avoids roundoff error for special cases such
as
degrees()
sind(30)
.