Skip to content

float.md

Latest commit

 

History

History
270 lines (250 loc) · 13 KB

float.md

File metadata and controls

270 lines (250 loc) · 13 KB

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.