Mathematical Functions and Operators v13
Mathematical operators are provided for many Advanced Server types. For types without common mathematical conventions for all possible permutations (e.g., date/time types) the actual behavior is described in subsequent sections.
The following table shows the available mathematical operators:
Operator | Description | Example | Result |
---|---|---|---|
+ | Addition | 2 + 3 | 5 |
- | Subtraction | 2 – 3 | -1 |
* | Multiplication | 2 * 3 | 6 |
/ | Division (See the following note) | 4 / 2 | 2 |
** | Exponentiation Operator | 2 ** 3 | 8 |
Note
If the db_dialect
configuration parameter in the postgresql.conf
file is set to redwood
, then division of a pair of INTEGER
data types does not result in a truncated value. Any fractional result is retained as shown by the following example:
This behavior is compatible with Oracle databases where there is no native INTEGER
data type, and any INTEGER
data type specification is internally converted to NUMBER(38)
, which results in retaining any fractional result.
If the db_dialect
configuration parameter is set to postgres
, then division of a pair of INTEGER
data types results in a truncated value as shown by the following example:
This behavior is compatible with PostgreSQL databases where division involving any pair of INTEGER
, SMALLINT
, or BIGINT
data types results in truncation of the result. The same truncated result is returned by Advanced Server when db_dialect
is set to postgres
as shown in the previous example.
Note however, that even when db_dialect
is set to redwood
, only division with a pair of INTEGER
data types results in no truncation of the result. Division that includes only SMALLINT
or BIGINT
data types, with or without an INTEGER
data type, does result in truncation in the PostgreSQL fashion without retaining the fractional portion as shown by the following where INTEGER
and SMALLINT
are involved in the division:
The following table 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. 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 may therefore vary depending on the host system.
Function | Return Type | Description | Example | Result |
---|---|---|---|---|
ABS(x) | Same as x | Absolute value | ABS(-17.4) | 17.4 |
CEIL(DOUBLE PRECISION or NUMBER) | Same as input | Smallest integer not less than argument | CEIL(-42.8) | -42 |
EXP(DOUBLE PRECISION or NUMBER) | Same as input | Exponential | EXP(1.0) | 2.7182818284590452 |
FLOOR(DOUBLE PRECISION or NUMBER) | Same as input | Largest integer not greater than argument | FLOOR(-42.8) | 43 |
LN(DOUBLE PRECISION or NUMBER) | Same as input | Natural logarithm | LN(2.0) | 0.6931471805599453 |
LOG(b NUMBER, x NUMBER) | NUMBER | Logarithm to base b | LOG(2.0, 64.0) | 6.0000000000000000 |
MOD(y, x) | Same as argument types | Remainder of y/x | MOD(9, 4) | 1 |
NVL(x, y) | Same as argument types; where both arguments are of the same data type | If x is null, then NVL returns y | NVL(9, 0) | 9 |
POWER(a DOUBLE PRECISION, b DOUBLE PRECISION) | DOUBLE PRECISION | a raised to the power of b | POWER(9.0, 3.0) | 729.0000000000000000 |
POWER(a NUMBER, b NUMBER) | NUMBER | a raised to the power of b | POWER(9.0, 3.0) | 729.0000000000000000 |
ROUND(DOUBLE PRECISION or NUMBER) | Same as input | Round to nearest integer | ROUND(42.4) | 42 |
ROUND(v NUMBER, s INTEGER) | NUMBER | Round to s decimal places | ROUND(42.4382, 2) | 42.44 |
SIGN(DOUBLE PRECISION or NUMBER) | Same as input | Sign of the argument (-1, 0, +1) | SIGN(-8.4) | -1 |
SQRT(DOUBLE PRECISION or NUMBER) | Same as input | Square root | SQRT(2.0) | 1.414213562373095 |
TRUNC(DOUBLE PRECISION or NUMBER) | Same as input | Truncate toward zero | TRUNC(42.8) | 42 |
TRUNC(v NUMBER, s INTEGER) | NUMBER | Truncate to s decimal places | TRUNC(42.4382, 2) | 42.43 |
WIDTH_BUCKET(op NUMBER, b1 NUMBER, b2 NUMBER, count INTEGER) | INTEGER | Return the bucket to which op would be assigned in an equidepth histogram with count buckets, in the range b1 to b2 | WIDTH_BUCKET(5.35, 0.024, 10.06, 5) | 3 |
The following table shows the available trigonometric functions. All trigonometric functions take arguments and return values of type DOUBLE PRECISION
.
Function | Description |
---|---|
ACOS(x) | Inverse cosine |
ASIN(x) | Inverse sine |
ATAN(x) | Inverse tangent |
ATAN2(x, y) | Inverse tangent of x/y |
COS(x) | Cosine |
SIN(x) | Sine |
TAN(x) | Tangent |