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.
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.
ABS(x) Same as x LOG(b NUMBER, x NUMBER) MOD(y, x) Remainder of y/x NVL(x, y) If x is null, then NVL returns y POWER(a DOUBLE PRECISION, b DOUBLE PRECISION) a raised to the power of b POWER(a NUMBER, b NUMBER) a raised to the power of b ROUND(v NUMBER, s INTEGER) Round to s decimal places TRUNC(v NUMBER, s INTEGER) WIDTH_BUCKET(op NUMBER, b1 NUMBER, b2 NUMBER, count INTEGER) Return the bucket to which op would be assigned in an equidepth histogram with count buckets, in the range b1 to b2 The following table shows the available trigonometric functions. All trigonometric functions take arguments and return values of type DOUBLE PRECISION.
ACOS(x) ASIN(x) ATAN(x) ATAN2(x, y) COS(x) SIN(x) TAN(x)