DOUBLE PRECISION (FLOAT)
Vertica supports the numeric data type DOUBLE PRECISION
, which is the IEEE754 8byte floating point type, along with most of the usual floating point operations.
Syntax
[ DOUBLE PRECISION  FLOAT  FLOAT(n)  FLOAT8  REAL ]
Parameters
Note
On a machine whose floatingpoint arithmetic does not follow IEEE754, these values probably do not work as expected.Double precision is an inexact, variableprecision 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 64bit IEEE FLOAT. 
The n in
FLOAT(n)
must be between 1 and 53, inclusive, but a 53bit fraction is always used. See the IEEE754 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 floatingpoint 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 floatingpoint 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 nonempty 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 nonempty 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 nonempty 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 nonempty 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 implementationdependent 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'
Note
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, Nonsignaling NaN.
Vertica uses a different NaN value to represent floating point NULL (0x7ffffffffffffffeLL). This is a Positive, Quiet, Nonsignaling 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.