DOUBLE PRECISION (FLOAT)
Vertica supports the numeric data type DOUBLE PRECISION
, which is the IEEE-754 8-byte floating point type, along with most of the usual floating point operations.
Syntax
[ DOUBLE PRECISION | FLOAT | FLOAT(n) | FLOAT8 | REAL ]
Parameters
On a machine whose floating-point arithmetic does not follow IEEE-754, these values probably do not work as expected.
Double precision is an inexact, variable-precision numeric type. In other words, some values cannot be represented exactly and are stored as approximations. Thus, input and output operations involving double precision might show slight discrepancies.
- All of the
DOUBLE PRECISION
data types are synonyms for 64-bit IEEE FLOAT. - The n in
FLOAT(n)
must be between 1 and 53, inclusive, but a 53-bit fraction is always used. See the IEEE-754 standard for details. - For exact numeric storage and calculations (money for example), use
NUMERIC
. - Floating point calculations depend on the behavior of the underlying processor, operating system, and compiler.
- Comparing two floating-point values for equality might not work as expected.
- While Vertica treats decimal values as
FLOAT
internally, if a column is defined asFLOAT
then you cannot read decimal values from ORC and Parquet files. In those formats,FLOAT
andDECIMAL
are different types.
Values
COPY
accepts floating-point data in the following format:
- Optional leading white space
- An optional plus ("+") or minus sign ("-")
- A decimal number, a hexadecimal number, an infinity, a NAN, or a null value
Decimal Number
A decimal number consists of a non-empty sequence of decimal digits possibly containing a radix character (decimal point "."), optionally followed by a decimal exponent. A decimal exponent consists of an "E" or "e", followed by an optional plus or minus sign, followed by a non-empty sequence of decimal digits, and indicates multiplication by a power of 10.
Hexadecimal Number
A hexadecimal number consists of a "0x" or "0X" followed by a non-empty sequence of hexadecimal digits possibly containing a radix character, optionally followed by a binary exponent. A binary exponent consists of a "P" or "p", followed by an optional plus or minus sign, followed by a non-empty sequence of decimal digits, and indicates multiplication by a power of 2. At least one of radix character and binary exponent must be present.
Infinity
An infinity is either INF
or INFINITY
, disregarding case.
NaN (Not A Number)
A NaN is NAN
(disregarding case) optionally followed by a sequence of characters enclosed in parentheses. The character string specifies the value of NAN in an implementation-dependent manner. (The Vertica internal representation of NAN is 0xfff8000000000000LL on x86 machines.)
When writing infinity or NAN values as constants in a SQL statement, enclose them in single quotes. For example:
=> UPDATE table SET x = 'Infinity'
Vertica follows the IEEE definition of NaNs (IEEE 754). The SQL standards do not specify how floating point works in detail.
IEEE defines NaNs as a set of floating point values where each one is not equal to anything, even to itself. A NaN is not greater than and at the same time not less than anything, even itself. In other words, comparisons always return false whenever a NaN is involved.
However, for the purpose of sorting data, NaN values must be placed somewhere in the result. The value generated 'NaN' appears in the context of a floating point number matches the NaN value generated by the hardware. For example, Intel hardware generates (0xfff8000000000000LL), which is technically a Negative, Quiet, Non-signaling NaN.
Vertica uses a different NaN value to represent floating point NULL (0x7ffffffffffffffeLL). This is a Positive, Quiet, Non-signaling NaN and is reserved by Vertica
A NaN example follows.
=> SELECT CBRT('Nan'); -- cube root
CBRT
------
NaN
(1 row)
=> SELECT 'Nan' > 1.0; ?column? ---------- f (1 row)
Null Value
The load file format of a null value is user defined, as described in the COPY
command. The Vertica internal representation of a null value is 0x7fffffffffffffffLL. The interactive format is controlled by the vsql printing option null. For example:
\pset null '(null)'
The default option is not to print anything.
Rules
- -0 == +0
- 1/0 = Infinity
- 0/0 == Nan
- NaN != anything (even NaN)
To search for NaN column values, use the following predicate:
... WHERE column != column
This is necessary because WHERE
column = 'Nan'
cannot be true by definition.
Sort Order (Ascending)
- NaN
- -Inf
- numbers
- +Inf
- NULL
Notes
NULL
appears last (largest) in ascending order.- All overflows in floats generate +/-infinity or NaN, per the IEEE floating point standard.