# 2.4.3 Mathematical Functions and Operators

2 The SQL Language : 2.4 Functions and Operators : 2.4.3 Mathematical Functions and Operators

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.
 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:
 Function Return Type Description Example Result ABS(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 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
 Function Description ACOS(x) Inverse cosine ASIN(x) Inverse sine ATAN(x) Inverse tangent ATAN2(x, y) COS(x) Cosine SIN(x) Sine TAN(x) Tangent

2 The SQL Language : 2.4 Functions and Operators : 2.4.3 Mathematical Functions and Operators