Vertica Analytics Platform Version 9.2.x Documentation

# 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 as`FLOAT`

then you cannot read decimal values from ORC and Parquet files. In those formats,`FLOAT`

and`DECIMAL`

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:

... WHEREcolumn!=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.

**Was this topic helpful?**