DOUBLE Type
Double precision is a numeric type with variable precision that may not always represent values exactly. In other words, some values are approximated rather than stored precisely. As a result, input and output operations involving double precision can sometimes lead to small discrepancies.
Syntax
DOUBLE
Limits
The range for a DOUBLE is:
Highest number (positive): 1.7976931348623157e+308
Lowest number (negative): -1.7976931348623157e+308
Literals
decimal_digits { D | exponent [ D ] }
Examples
> SELECT CAST('1.23' AS DOUBLE);
1.23
> SELECT 1e-10;
0.0000000001
> SELECT 1e-1000;
0
Handling NaN
NaN stands for “Not a Number.” It is an IEEE standard special floating-point value used to represent undefined or unrepresentable numerical results, such as the result of 0/0 or the square root of a negative number.
Notes
The comparison logic for NaN varies between Vertica and Spark-based engine. In Spark-based engine, NaN is considered equal to NaN, and NaN is considered equal to -NaN. In Vertica, both of these comparisons evaluate to false.
-- Comparison of NaN values
SELECT CAST('NaN' AS DOUBLE) = CAST('NaN' AS DOUBLE);
-- Result: false (Vertica)
-- Result: true (Spark-based engine)
-- Comparison of positive and negative NaN values
SELECT CAST('NaN' AS DOUBLE) = CAST('-NaN' AS DOUBLE);
-- Result: false (Vertica)
-- Result: true (Spark-based engine)