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
andy > 0
then:x - (floor(x / y) * y)
- if
x >= 0
andy < 0
then:x % abs(y)
- if
x < 0
andy > 0
then:- abs(x) % y
- if
x < 0
andy > 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) |
TRUNC(value, digits) | Same as TRUNCATE . |
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 NaN for a negative value. |
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. |