8.2 Mathematical Functions

8.2.1 ABS()

Available inDSQL, PSQL

Possible name conflictYES → Read details

Result typeNumerical

Syntax

ABS (number)

Table 8.2.1.1 ABS Function Parameter

Returns the absolute value of the argument.

8.2.2 ACOS()

Available inDSQL, PSQL

Possible name conflictYES → Read details

Result typeDOUBLE PRECISION

Syntax

ACOS (number)

Table 8.2.2.1 ACOS Function Parameter

Returns the arc cosine of the argument.

• The result is an angle in the range [0, pi].

See alsoSection 8.2.10, COS(), Section 8.2.4, ASIN(), Section 8.2.6, ATAN()

8.2.3 ACOSH()

Available inDSQL, PSQL

Result typeDOUBLE PRECISION

Syntax

ACOSH (number)

Table 8.2.3.1 ACOSH Function Parameter

Returns the inverse hyperbolic cosine of the argument.

• The result is in the range [0, INF].

See alsoSection 8.2.11, COSH(), Section 8.2.5, ASINH(), Section 8.2.8, ATANH()

8.2.4 ASIN()

Available inDSQL, PSQL

Possible name conflictYES → Read details

Result typeDOUBLE PRECISION

Syntax

ASIN (number)

Table 8.2.4.1 ASIN Function Parameter

Returns the arc sine of the argument.

• The result is an angle in the range [-pi/2, pi/2].

See alsoSection 8.2.24, SIN(), Section 8.2.2, ACOS(), Section 8.2.6, ATAN()

8.2.5 ASINH()

Available inDSQL, PSQL

Result typeDOUBLE PRECISION

Syntax

ASINH (number)

Table 8.2.5.1 ASINH Function Parameter

Returns the inverse hyperbolic sine of the argument.

• The result is in the range [-INF, INF].

See alsoSection 8.2.25, SINH(), Section 8.2.3, ACOSH(), Section 8.2.8, ATANH()

8.2.6 ATAN()

Available inDSQL, PSQL

Possible name conflictYES → Read details

Result typeDOUBLE PRECISION

Syntax

ATAN (number)

Table 8.2.6.1 ATAN Function Parameter

The function ATAN returns the arc tangent of the argument. The result is an angle in the range <-pi/2, pi/2>.

See alsoSection 8.2.7, ATAN2(), Section 8.2.27, TAN(), Section 8.2.2, ACOS(), Section 8.2.4, ASIN()

8.2.7 ATAN2()

Available inDSQL, PSQL

Possible name conflictYES → Read details

Result typeDOUBLE PRECISION

Syntax

ATAN2 (y, x)

Table 8.2.7.1 ATAN2 Function Parameters

Returns the angle whose sine-to-cosine ratio is given by the two arguments, and whose sine and cosine signs correspond to the signs of the arguments. This allows results across the entire circle, including the angles -pi/2 and pi/2.

• The result is an angle in the range [-pi, pi].

• If x is negative, the result is pi if y is 0, and -pi if y is -0.

• If both y and x are 0, the result is meaningless. An error will be raised if both arguments are 0.

• A fully equivalent description of this function is the following: ATAN2(*y*, *x*) is the angle between the positive X-axis and the line from the origin to the point (x, y). This also makes it obvious that ATAN2(0, 0) is undefined.

• If x is greater than 0, ATAN2(*y*, *x*) is the same as ATAN(*y*/*x*).

• If both sine and cosine of the angle are already known, ATAN2(*sin*, *cos*) gives the angle.

8.2.8 ATANH()

Available inDSQL, PSQL

Result typeDOUBLE PRECISION

Syntax

ATANH (number)

Table 8.2.8.1 ATANH Function Parameter

Returns the inverse hyperbolic tangent of the argument.

• The result is a number in the range [-INF, INF].

See alsoSection 8.2.28, TANH(), Section 8.2.3, ACOSH(), Section 8.2.5, ASINH()

8.2.9 CEIL(), CEILING()

Available inDSQL, PSQL

Possible name conflictYES → Read details (Affects CEILING only)

Result typeBIGINT for exact numeric number, or DOUBLE PRECISION for floating point number

Syntax

CEIL[ING] (number)

Table 8.2.9.1 CEIL[ING] Function Parameters

Returns the smallest whole number greater than or equal to the argument.

See alsoSection 8.2.14, FLOOR(), Section 8.2.22, ROUND(), Section 8.2.29, TRUNC()

8.2.10 COS()

Available inDSQL, PSQL

Possible name conflictYES → Read details

Result typeDOUBLE PRECISION

Syntax

COS (angle)

Table 8.2.10.1 COS Function Parameter

Returns an angle’s cosine. The argument must be given in radians.

• Any non-NULL result is — obviously — in the range [-1, 1].

See alsoSection 8.2.2, ACOS(), Section 8.2.12, COT(), Section 8.2.24, SIN(), Section 8.2.27, TAN()

8.2.11 COSH()

Available inDSQL, PSQL

Possible name conflictYES → Read details

Result typeDOUBLE PRECISION

Syntax

COSH (number)

Table 8.2.11.1 COSH Function Parameter

Returns the hyperbolic cosine of the argument.

• Any non-NULL result is in the range [1, INF].

See alsoSection 8.2.3, ACOSH(), Section 8.2.25, SINH(), Section 8.2.28, TANH()

8.2.12 COT()

Available inDSQL, PSQL

Possible name conflictYES → Read details

Result typeDOUBLE PRECISION

Syntax

COT (angle)

Table 8.2.12.1 COT Function Parameter

Returns an angle’s cotangent. The argument must be given in radians.

See alsoSection 8.2.10, COS(), Section 8.2.24, SIN(), Section 8.2.27, TAN()

8.2.13 EXP()

Available inDSQL, PSQL

Result typeDOUBLE PRECISION

Syntax

EXP (number)

Table 8.2.13.1 EXP Function Parameter

Returns the natural exponential, enumber

See alsoSection 8.2.15, LN()

8.2.14 FLOOR()

Available inDSQL, PSQL

Possible name conflictYES → Read details

Result typeBIGINT for exact numeric number, or DOUBLE PRECISION for floating point number

Syntax

FLOOR (number)

Table 8.2.14.1 FLOOR Function Parameter

Returns the largest whole number smaller than or equal to the argument.

See alsoSection 8.2.9, CEIL(), CEILING(), Section 8.2.22, ROUND(), Section 8.2.29, TRUNC()

8.2.15 LN()

Available inDSQL, PSQL

Possible name conflictYES → Read details

Result typeDOUBLE PRECISION

Syntax

LN (number)

Table 8.2.15.1 LN Function Parameter

Returns the natural logarithm of the argument.

• An error is raised if the argument is negative or 0.

See alsoSection 8.2.13, EXP(), Section 8.2.16, LOG(), Section 8.2.17, LOG10()

8.2.16 LOG()

Available inDSQL, PSQL

Possible name conflictYES → Read details

Result typeDOUBLE PRECISION

Syntax

LOG (x, y)

Table 8.2.16.1 LOG Function Parameters

Returns the x-based logarithm of y.

• If either argument is 0 or below, an error is raised. (Before 2.5, this would result in NaN, +/-INF or 0, depending on the exact values of the arguments.)

• If both arguments are 1, NaN is returned.

• If x = 1 and y < 1, -INF is returned.

• If x = 1 and y > 1, INF is returned.

See alsoSection 8.2.20, POWER(), Section 8.2.15, LN(), Section 8.2.17, LOG10()

8.2.17 LOG10()

Available inDSQL, PSQL

Possible name conflictYES → Read details

Result typeDOUBLE PRECISION

Syntax

LOG10 (number)

Table 8.2.17.1 LOG10 Function Parameter

Returns the 10-based logarithm of the argument.

• An error is raised if the argument is negative or 0. (In versions prior to 2.5, such values would result in NaN and -INF, respectively.)

See alsoSection 8.2.20, POWER(), Section 8.2.15, LN(), Section 8.2.16, LOG()

8.2.18 MOD()

Available inDSQL, PSQL

Possible name conflictYES → Read details

Result typeSMALLINT, INTEGER or BIGINT depending on the type of a. If a is a floating-point type, the result is a BIGINT.

Syntax

MOD (a, b)

Table 8.2.18.1 MOD Function Parameters

Returns the remainder of an integer division.

• Non-integer arguments are rounded before the division takes place. So, mod(7.5, 2.5) gives 2 (mod(8, 3)), not 0.

8.2.19 PI()

Available inDSQL, PSQL

Possible name conflictYES → Read details

Result typeDOUBLE PRECISION

Syntax

PI ()

Returns an approximation of the value of pi.

8.2.20 POWER()

Available inDSQL, PSQL

Possible name conflictYES → Read details

Result typeDOUBLE PRECISION

Syntax

POWER (x, y)

Table 8.2.20.1 POWER Function Parameters

Returns x to the power of y (xy).

See alsoSection 8.2.13, EXP(), Section 8.2.16, LOG(), Section 8.2.17, LOG10(), Section 8.2.26, SQRT()

8.2.21 RAND()

Available inDSQL, PSQL

Possible name conflictYES → Read details

Result typeDOUBLE PRECISION

Syntax

RAND ()

Returns a random number between 0 and 1.

8.2.22 ROUND()

Available inDSQL, PSQL

Possible name conflictYES → Read details

Result typeINTEGER, (scaled) BIGINT or DOUBLE PRECISION

Syntax

ROUND (number [, scale])

Table 8.2.22.1 ROUND Function Parameters

Rounds a number to the nearest integer. If the fractional part is exactly 0.5, rounding is upward for positive numbers and downward for negative numbers. With the optional scale argument, the number can be rounded to powers-of-ten multiples (tens, hundreds, tenths, hundredths, etc.) instead of just integers.

Important

If you are used to the behaviour of the external function ROUND, please notice that the internal function always rounds halves away from zero, i.e. downward for negative numbers.

8.2.22.1 ROUND Examples

If the scale argument is present, the result usually has the same scale as the first argument:

ROUND(123.654, 1) -- returns 123.700 (not 123.7)
ROUND(8341.7, -3) -- returns 8000.0 (not 8000)
ROUND(45.1212, 0) -- returns 45.0000 (not 45)

Otherwise, the result scale is 0:

ROUND(45.1212) -- returns 45

See alsoSection 8.2.9, CEIL(), CEILING(), Section 8.2.14, FLOOR(), Section 8.2.29, TRUNC()

8.2.23 SIGN()

Available inDSQL, PSQL

Possible name conflictYES → Read details

Result typeSMALLINT

Syntax

SIGN (number)

Table 8.2.23.1 SIGN Function Parameter

Returns the sign of the argument: -1, 0 or 1.

8.2.24 SIN()

Available inDSQL, PSQL

Possible name conflictYES → Read details

Result typeDOUBLE PRECISION

Syntax

SIN (angle)

Table 8.2.24.1 SIN Function Parameter

Returns an angle’s sine. The argument must be given in radians.

• Any non-NULL result is — obviously — in the range [-1, 1].

See alsoSection 8.2.4, ASIN(), Section 8.2.10, COS(), Section 8.2.12, COT(), Section 8.2.27, TAN()

8.2.25 SINH()

Available inDSQL, PSQL

Possible name conflictYES → Read details

Result typeDOUBLE PRECISION

Syntax

SINH (number)

Table 8.2.25.1 SINH Function Parameter

Returns the hyperbolic sine of the argument.

See alsoSection 8.2.5, ASINH(), Section 8.2.11, COSH(), Section 8.2.28, TANH()

8.2.26 SQRT()

Available inDSQL, PSQL

Possible name conflictYES → Read details

Result typeDOUBLE PRECISION

Syntax

SQRT (number)

Table 8.2.26.1 SQRT Function Parameter

Returns the square root of the argument.

• If number is negative, an error is raised.

See alsoSection 8.2.20, POWER()

8.2.27 TAN()

Available inDSQL, PSQL

Possible name conflictYES → Read details

Result typeDOUBLE PRECISION

Syntax

TAN (angle)

Table 8.2.27.1 TAN Function Parameter

Returns an angle’s tangent. The argument must be given in radians.

See alsoSection 8.2.6, ATAN(), Section 8.2.7, ATAN2(), Section 8.2.10, COS(), Section 8.2.12, COT(), Section 8.2.24, SIN(), Section 8.2.27, TAN()

8.2.28 TANH()

Available inDSQL, PSQL

Possible name conflictYES → Read details

Result typeDOUBLE PRECISION

Syntax

TANH (number)

Table 8.2.28.1 TANH Function Parameters

Returns the hyperbolic tangent of the argument.

• Due to rounding, any non-NULL result is in the range [-1, 1] (mathematically, it’s <-1, 1>).

See alsoSection 8.2.8, ATANH(), Section 8.2.11, COSH(), Section 8.2.28, TANH()

8.2.29 TRUNC()

Available inDSQL, PSQL

Result typeINTEGER, (scaled) BIGINT or DOUBLE PRECISION

Syntax

TRUNC (number [, scale])

Table 8.2.29.1 TRUNC Function Parameters

Returns the integer part of a number. With the optional scale argument, the number can be truncated to powers-of-ten multiples (tens, hundreds, tenths, hundredths, etc.) instead of just integers.

Note

• If the scale argument is present, the result usually has the same scale as the first argument, e.g.

  • TRUNC(789.2225, 2) returns 789.2200 (not 789.22)

  • TRUNC(345.4, -2) returns 300.0 (not 300)

  • TRUNC(-163.41, 0) returns -163.00 (not -163)

• Otherwise, the result scale is 0:

  • TRUNC(-163.41) returns -163

Important

If you are used to the behaviour of the external function TRUNCATE, please notice that the internal function TRUNC always truncates toward zero, i.e. upward for negative numbers.

See alsoSection 8.2.9, CEIL(), CEILING(), Section 8.2.14, FLOOR(), Section 8.2.22, ROUND()