Skip to main content

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)