Mathematical functions and operators v17
Overview of mathematical operators
Mathematical operators are provided for many EDB Postgres Advanced Server types. 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 doesn't result in a truncated value. Any fractional result is retained as shown by the following example:
edb=# SET db_dialect TO redwood; SET edb=# SHOW db_dialect;
db_dialect ------------ redwood (1 row)
edb=# SELECT CAST('10' AS INTEGER) / CAST('3' AS INTEGER) FROM dual;
?column?
--------------------
3.3333333333333333
(1 row)This behavior is compatible with Oracle databases where there is no native INTEGER data type. 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:
edb=# SET db_dialect TO postgres; SET edb=# SHOW db_dialect;
db_dialect ------------ postgres (1 row)
edb=# SELECT CAST('10' AS INTEGER) / CAST('3' AS INTEGER) FROM dual;
?column?
----------
3
(1 row)This behavior is compatible with PostgreSQL databases where division involving any pair of INTEGER, SMALLINT, or BIGINT data types results in truncating the result. The same truncated result is returned by EDB Postgres Advanced Server when db_dialect is set to postgres.
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, results in truncation in the PostgreSQL fashion without retaining the fractional portion. In this example, INTEGER and SMALLINT are involved in the division:
edb=# SHOW db_dialect;
db_dialect ------------ redwood (1 row)
edb=# SELECT CAST('10' AS INTEGER) / CAST('3' AS SMALLINT) FROM dual;
?column?
----------
3
(1 row)Available mathematical functions
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 might 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 |
Available trigonometric functions
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 |