Floating Point Operations

We support standard IEEE 754 floating point types.

double is a 64-bit standard FP value. Accepted synonyms are float8 and float64.

float is a 32-bit standard FP value. Accepted synonyms are float4, and float32.

Floating point values include special values, such as NaN (not a number), -Infinity, and -Infinity. An alternative spelling for -Infinity is -inf, and an alternative spelling for Infinity is inf, and an alternative spelling for ‘NaN’ is ‘nan’. When written as SQL literals, these values have to be surrounded by simple quotes: 'inf'. Please note that these strings are case-sensitive and spaces are ignored.

Infinity plus any finite value equals Infinity, as does Infinity plus Infinity. Infinity minus Infinity yields NaN.

NaN (not a number) value is used to represent undefined results. An operation with a NaN input yields NaN. The only exception is when the operation’s output does not depend on the NaN value: an example is NaN raised to the zero power yields one.

In sorting order NaN is considered greater than all other values.

The legal operations are + (plus, unary and binary), - (minus, unary and binary), * (multiplication), / (division), % (modulus).

Modulus happens as follows: For: mod = x % y

if x >= 0 and y > 0 then: x - (floor(x / y) * y)
if x >= 0 and y < 0 then: x % abs(y)
if x < 0 and y > 0 then: - abs(x) % y
if x < 0 and y > 0 then: - abs(x) % abs(y)

Division by zero returns Infinity, (or NaN in case of 0e0 / 0e0). Modulus by zero return NaN.

Casting a string to a floating-point value will produce the value 0 when parsing fails.

Casting a value that is out of the supported range to a floating point type will produce a value that is inf or -inf.

Casting a floating-point value to string, float is rounded off to 6 decimal places and double is rounded off to 15 decimal places.

Please note that numeric values with a decimal point have the decimal type by default. To write a floating-point literal you have to include the e for exponent using the following grammar:

digits.digits[e[+-]digits]

[digits].digits[e[+-]digits]

Alternatively, you can use an explicit cast:


REAL '1.23'  -- string style
1.23::REAL   -- PostgreSQL style

Predefined functions on Floating-point Values

Function	Types	Description
`ABS(value)`	`REAL`, `DOUBLE`	absolute value
`ACOS(value)`	`DOUBLE`	The arccosine of the value, returned as radians. The returned value is in the range `[0, pi]` or `NaN` if the value is outside the range of `[-1, 1]`. `acos` only supports arguments of type double, so all other types are cast to double. Returns a double.
`ACOSH(value)`	`DOUBLE`	The hyperbolic arccosine of the value, returned as radians. `acosh` only supports arguments of type double, so all other types are cast to double. Returns a double.
`ASIN(value)`	`DOUBLE`	The arcsine of the value, returned as radians. The returned value is in the range `[-pi/2, pi/2]` or `NaN` if the value is outside the range of `[-1, 1]`. `asin` only supports arguments of type double, so all other types are cast to double. Returns a double.
`ASINH(value)`	`DOUBLE`	The hyperbolic arcsine of the value, returned as radians. `asinh` only supports arguments of type double, so all other types are cast to double. Returns a double.
`ATAN(value)`	`DOUBLE`	The arctangent of the value, returned as radians. The returned value is in the range `[-pi/2, pi/2]`.`atan` only supports arguments of type double, so all other types are cast to double. Returns a double.
`ATAN2(y, x)`	`DOUBLE`	The arctangent of `y/x`, returned as radians. `atan2` only supports arguments of type double, so all other types are cast to double. Returns a double.
`ATANH(value)`	`DOUBLE`	The hyperbolic arctangent of the value, returned as radians. `atanh` only supports arguments of type double, so all other types are cast to double. Returns a double.
`CBRT(value)`	`DOUBLE`	Calculates the cube root of the given value. `cbrt` only supports arguments of type double, so all other types are cast to double. Returns a double.
`CEIL(value)`	`REAL`, `DOUBLE`	Ceiling function: nearest integer value greater than or equal to argument (result is a floating point value)
`COS(value)`	`DOUBLE`	The cosine of value as radians. `cos` only supports argument of type double, so all other types are cast to double. Returns a double.
`COSH(value)`	`DOUBLE`	The hyperbolic cosine of the value as radians. `cosh` only supports arguments of type double, so all other types are cast to double. Returns a double.
`CSC(value)`	`DOUBLE`	The cosecant of the value as radians. `csc` only supports arguments of type double, so all other types are cast to double. Returns a double. For undefined values of `csc` (like `csc(pi)`) a fixed arbitrary value may be returned.
`CSCH(value)`	`DOUBLE`	The hyperbolic cosecant of the value as radians. `csch` only supports arguments of type double, so all other types are cast to double. Returns a double.
`COT(value)`	`DOUBLE`	The cotangent of the value as radians. `cot` only supports arguments of type double, so all other types are cast to double. Returns a double. For undefined values of `cot` (like `cot(pi)`) a fixed arbitrary value may be returned.
`COTH(value)`	`DOUBLE`	The hyperbolic cotangent of the value as radians. `coth` only supports arguments of type double, so all other types are cast to double. Returns a double.
`DEGREES(value)`	`DOUBLE`	Converts the given value in radians to degrees. `degrees` only supports arguments of type double, so all other types are cast to double. Returns a double.
`EXP(value)`	`DOUBLE`	Returns e raised to the power of value (exponential).
`FLOOR(value)`	`REAL`, `DOUBLE`	Floor function: nearest integer value less than or equal to argument (result is a floating point value)
`IS_INF(value)`	`REAL`, `DOUBLE`	Returns true if the value is infinite.
`IS_NAN(value)`	`REAL`, `DOUBLE`	Returns true if the value is NaN. Note that two NaN values may not be equal.
`LN(value)`	`DOUBLE`	The natural logarithm of value. Returns `-inf` for 0. Produces a runtime error for negative numbers.
`LOG(value, [, base])`	`DOUBLE`	The logarithm of value to base, or base e if it is not present. Produces a runtime error for negative values for either value or base. Returns `-inf` for base 0.
`LOG10(value)`	`DOUBLE`	The logarithm base 10 of value. Returns `-inf` for 0. Produces a runtime error for negative numbers.
`PI`	`DOUBLE`	Returns the approximate value of `PI` as double. Note that `()` is not allowed. Example: `SELECT PI;`
`POWER(BASE, EXPONENT)`	`DOUBLE`	The power function, raising `BASE` to the power `EXPONENT`. The exponent can have type `DOUBLE` or `INTEGER`.
`RADIANS(value)`	`DOUBLE`	Converts the given value in degrees to radians. `radians` only supports arguments of type double, so all other types are cast to double. Returns a double.
`ROUND(value)`	`REAL`, `DOUBLE`	Rounds to the nearest integer; the result has the same type as the input. Rounding follows "Bankers Rounding" (rounds 0.5 to the nearest even number) strategy.
`ROUND(value, digits)`	`REAL`, `DOUBLE`	where `digits` is an integer value. Round the value to the specified number of decimal digits after the decimal point; the result has the same type as the input.
`SEC(value)`	`DOUBLE`	The secant of the value as radians. `sec` only supports arguments of type double, so all other types are cast to double. Returns a double. For undefined values of `sec` (like `sec(pi / 2)`) a fixed arbitrary value may be returned.
`SECH(value)`	`DOUBLE`	The hyperbolic secant of the value as radians. `sech` only supports arguments of type double, so all other types are cast to double. Returns a double.
`SIN(value)`	`DOUBLE`	The sine of value as radians. `sin` only supports argument of type double, so all other types are cast to double. Returns a double.
`SINH(value)`	`DOUBLE`	The hyperbolic sine of the value as radians. `sinh` only supports arguments of type double, so all other types are cast to double. Returns a double.
`SQRT(value)`	`DOUBLE`	Square root of value. Produces NaN for a negative value.
`TAN(value)`	`DOUBLE`	The tangent of the value as radians. `tan` only supports arguments of type double, so all other types are cast to double. Returns a double. For undefined values of `tan` (like `tan(pi / 2)`) a fixed arbitrary value may be returned.
`TANH(value)`	`DOUBLE`	The hyperbolic tangent of the value as radians. `tanh` only supports arguments of type double, so all other types are cast to double. Returns a double.
`TRUNC(value, digits)`	`REAL`, `DOUBLE`	Same as `TRUNCATE`.
`TRUNCATE(value)`	`REAL`, `DOUBLE`	Returns the integer portion of the number. This is true truncation, no rounding is performed.
`TRUNCATE(value, digits)`	`REAL`, `DOUBLE`	where `digits` is an integer value. Truncates the value to the specified number of decimal digits after the decimal point.