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 means “Not a Number”. It is an IEEE standard special floating-point value, which is used to represent undefined or unrepresentable numerical results, such as the result of 0/0 or the square root of a negative number.
Notes
NaN behaves differently in Vertica and Databricks. Databricks considers NaN to be equal to NaN, and NaN to be equal to -NaN. Vertica considers both of these comparisons to be false.
> SELECT CAST('NaN' AS DOUBLE) = CAST('NaN' AS DOUBLE);
false (Vertica)
true (Databricks)
> SELECT CAST('NaN' AS DOUBLE) = CAST('-NaN' AS DOUBLE);
false (Vertica)
true (Databricks)