Mathematical Functions and Operators v11

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:

OperatorDescriptionExampleResult
+Addition2 + 35
-Subtraction2 – 3-1
*Multiplication2 * 36
/Division (See the following note)4 / 22
**Exponentiation Operator2 ** 38
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:

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, 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:

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 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:

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)

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.

FunctionReturn TypeDescriptionExampleResult
ABS(x)Same as xAbsolute valueABS(-17.4)17.4
CEIL(DOUBLE PRECISION or NUMBER)Same as inputSmallest integer not less than argumentCEIL(-42.8)-42
EXP(DOUBLE PRECISION or NUMBER)Same as inputExponentialEXP(1.0)2.7182818284590452
FLOOR(DOUBLE PRECISION or NUMBER)Same as inputLargest integer not greater than argumentFLOOR(-42.8)43
LN(DOUBLE PRECISION or NUMBER)Same as inputNatural logarithmLN(2.0)0.6931471805599453
LOG(b NUMBER, x NUMBER)NUMBERLogarithm to base bLOG(2.0, 64.0)6.0000000000000000
MOD(y, x)Same as argument typesRemainder of y/xMOD(9, 4)1
NVL(x, y)Same as argument types; where both arguments are of the same data typeIf x is null, then NVL returns yNVL(9, 0)9
POWER(a DOUBLE PRECISION, b DOUBLE PRECISION)DOUBLE PRECISIONa raised to the power of bPOWER(9.0, 3.0)729.0000000000000000
POWER(a NUMBER, b NUMBER)NUMBERa raised to the power of bPOWER(9.0, 3.0)729.0000000000000000
ROUND(DOUBLE PRECISION or NUMBER)Same as inputRound to nearest integerROUND(42.4)42
ROUND(v NUMBER, s INTEGER)NUMBERRound to s decimal placesROUND(42.4382, 2)42.44
SIGN(DOUBLE PRECISION or NUMBER)Same as inputSign of the argument (-1, 0, +1)SIGN(-8.4)-1
SQRT(DOUBLE PRECISION or NUMBER)Same as inputSquare rootSQRT(2.0)1.414213562373095
TRUNC(DOUBLE PRECISION or NUMBER)Same as inputTruncate toward zeroTRUNC(42.8)42
TRUNC(v NUMBER, s INTEGER)NUMBERTruncate to s decimal placesTRUNC(42.4382, 2)42.43
WIDTH_BUCKET(op NUMBER, b1 NUMBER, b2 NUMBER, count INTEGER)INTEGERReturn the bucket to which op would be assigned in an equidepth histogram with count buckets, in the range b1 to b2WIDTH_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.

FunctionDescription
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