# Mathematical functions and operators v16

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

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:

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:

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