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 mathematical operators that are available for the standard numeric types. Unless otherwise noted, operators shown as accepting numeric_type
are available for all the types smallint
, integer
, bigint
, numeric
, real
, and double precision
. Operators shown as accepting integral_type
are available for the types smallint
, integer
, and bigint
. Except where noted, each form of an operator returns the same data type as its argument(s). Calls involving multiple argument data types, such as integer
+
numeric
, are resolved by using the type appearing later in these lists.
Table 9.4. Mathematical Operators
Operator Description Example(s) |
---|
Addition
|
Unary plus (no operation)
|
Subtraction
|
Negation
|
Multiplication
|
Division (for integral types, division truncates the result towards zero)
|
Modulo (remainder); available for
|
Exponentiation
Unlike typical mathematical practice, multiple uses of
|
Square root
|
Cube root
|
Absolute value
|
Bitwise AND
|
Bitwise OR
|
Bitwise exclusive OR
|
Bitwise NOT
|
Bitwise shift left
|
Bitwise shift right
|
Table 9.5 shows the available mathematical functions. 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(s); cross-type cases are resolved in the same way as explained above for operators. 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 random()
function uses a deterministic pseudo-random number generator. It is fast but not suitable for cryptographic applications; see the pgcrypto module for a more secure alternative. If setseed()
is called, the series of results of subsequent random()
calls in the current session can be repeated by re-issuing setseed()
with the same argument. Without any prior setseed()
call in the same session, the first random()
call obtains a seed from a platform-dependent source of random bits. These remarks hold equally for random_normal()
.
Table 9.7 shows the available trigonometric functions. Each of these functions comes in two variants, one that measures angles in radians and one that measures angles in degrees.
Table 9.7. Trigonometric Functions
Another way to work with angles measured in degrees is to use the unit transformation functions
and radians()
shown earlier. However, using the degree-based trigonometric functions is preferred, as that way avoids round-off error for special cases such as degrees()
sind(30)
.
Table 9.8 shows the available hyperbolic functions.
Table 9.8. Hyperbolic Functions
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.