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()
and random_normal()
functions listed in Table 9.6 use 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 calls to these functions 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 call to any of these functions obtains a seed
from a platform-dependent source of random bits.
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