Floating point types

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

ABS(value)	absolute value
CEIL(value)	Ceiling function: nearest integer value greater than or equal to argument (result is a floating point value)
FLOOR(value)	Floor function: nearest integer value less than or equal to argument (result is a floating point value)
TRUNCATE(value)	Returns the integer portion of the number. This is true truncation, no rounding is performed.
TRUNCATE(value, digits)	where digits is an integer value. Truncates the value to the specified number of decimal digits after the decimal point.
ROUND(value)	Rounds to the nearest integer and returns it. Rounding follows "Bankers Rounding" (rounds 0.5 to the nearest even number) strategy.
ROUND(value, digits)	where digits is an integer value. Round the value to the specified number of decimal digits after the decimal point.
POWER(BASE, EXPONENT)	The power function, raising BASE to the power EXPONENT
SQRT(value)	Square root of value. Produces a runtime error for negative values.
EXP(value)	Returns e raised to the power of value (exponential).
LN(value)	The natural logarithm of value. Returns `-inf` for 0. Produces a runtime error for negative numbers.
LOG(value, [, base])	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)	The logarithm base 10 of value. Returns `-inf` for 0. Produces a runtime error for negative numbers.
IS_INF(value)	Returns true if the value is infinite.
IS_NAN(value)	Returns true if the value is NaN. Note that two NaN values may not be equal.
SIN(value)	The sine of value as radians. sin only supports argument of type double, so all other types are cast to double. Returns a double.
COS(value)	The cosine of value as radians. cos only supports argument of type double, so all other types are cast to double. Returns a double.
PI	Returns the approximate value of PI as double. Note that () is not allowed. Example: SELECT PI;
TAN(value)	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.
COT(value)	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.
SEC(value)	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.
CSC(value)	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.
ASIN(value)	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.
ACOS(value)	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.
ATAN(value)	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)	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.
DEGREES(value)	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.
RADIANS(value)	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.
CBRT(value)	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.
SINH(value)	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.
COSH(value)	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.
TANH(value)	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.
COTH(value)	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.
SECH(value)	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.
CSCH(value)	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.
ASINH(value)	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.
ACOSH(value)	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.
ATANH(value)	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.