This is the multipage printable view of this section.
Click here to print.
Return to the regular view of this page.
Numeric data types
Numeric data types are numbers stored in database columns.
Numeric data types are numbers stored in database columns. These data types are typically grouped by:

Exact numeric types, values where the precision and scale need to be preserved. The exact numeric types are INTEGER
, BIGINT
, DECIMAL
, NUMERIC
, NUMBER
, and MONEY
.

Approximate numeric types, values where the precision needs to be preserved and the scale can be floating. The approximate numeric types are DOUBLE PRECISION
, FLOAT
, and REAL
.
Implicit casts from INTEGER
, FLOAT
, and NUMERIC
to VARCHAR
are not supported. If you need that functionality, write an explicit cast using one of the following forms:
CAST(numericexpression AS datatype)
numericexpression::datatype
For example, you can cast a float to an integer as follows:
=> SELECT(FLOAT '123.5')::INT;
?column?

124
(1 row)
Stringtonumeric data type conversions accept formats of quoted constants for scientific notation, binary scaling, hexadecimal, and combinations of numerictype literals:

Scientific notation:
=> SELECT FLOAT '1e10';
?column?

10000000000
(1 row)

BINARY
scaling:
=> SELECT NUMERIC '1p10';
?column?

1024
(1 row)

hexadecimal:
=> SELECT NUMERIC '0x0abc';
?column?

2748
(1 row)
1  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.
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 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 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
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.
2  INTEGER
A signed 8byte (64bit) data type.
A signed 8byte (64bit) data type.
Syntax
[ INTEGER  INT  BIGINT  INT8  SMALLINT  TINYINT ]
Parameters
INT
, INTEGER
, INT8
, SMALLINT
, TINYINT
, and BIGINT
are all synonyms for the same signed 64bit integer data type. Automatic compression techniques are used to conserve disk space in cases where the full 64 bits are not required.
Notes

The range of values is –2^63+1 to 2^631.

2^63 = 9,223,372,036,854,775,808 (19 digits).

The value –2^63 is reserved to represent NULL.

NULL
appears first (smallest) in ascending order.

Vertica does not have an explicit 4byte (32bit integer) or smaller types. Vertica's encoding and compression automatically eliminate the storage overhead of values that fit in less than 64 bits.
Restrictions

The JDBC type INTEGER is 4 bytes and is not supported by Vertica. Use BIGINT
instead.

Vertica does not support the SQL/JDBC types NUMERIC
, SMALLINT
, or TINYINT
.

Vertica does not check for overflow (positive or negative) except in the aggregate function SUM
()
. If you encounter overflow when using SUM
, use SUM_FLOAT
()
, which converts to floating point.
See also
Data Type Coercion Chart
3  NUMERIC
Numeric data types store fixedpoint numeric data.
Numeric data types store fixedpoint numeric data. For example, a value of $123.45 can be stored in a NUMERIC(5,2)
field. Note that the first number, the precision, specifies the total number of digits.
Syntax
numerictype [ ( precision[, scale] ) ]
Parameters
numerictype
 One of the following:

NUMERIC

DECIMAL

NUMBER

MONEY
precision
 An unsigned integer that specifies the total number of significant digits that the data type stores, where
precision
is ≤ 1024. If omitted, the default precision depends on numeric type that you specify. If you assign a value that exceeds precision
, Vertica returns an error.
If a data type's precision is ≤ 18, performance is equivalent to an INTEGER data type, regardless of scale. When possible, Vertica recommends using a precision ≤ 18.
scale
 An unsigned integer that specifies the maximum number of digits to the right of the decimal point to store.
scale
must be ≤ precision
. If omitted, the default scale depends on numeric type that you specify. If you assign a value with more decimal digits than scale
, the scale is rounded to scale
digits.
When using ALTER to modify the data type of a numeric column, scale
cannot be changed.
Default precision and scale
NUMERIC
, DECIMAL
, NUMBER
, and MONEY
differ in their default precision and scale values:
Type 
Precision 
Scale 
NUMERIC 
37 
15 
DECIMAL 
37 
15 
NUMBER 
38 
0 
MONEY 
18 
4 
Supported encoding
Vertica supports the following encoding for numeric data types:

Precision ≤ 18: AUTO
, BLOCK_DICT
, BLOCKDICT_COMP
, COMMONDELTA_COMP
, DELTAVAL
, GCDDELTA
, and RLE

Precision > 18: AUTO
, BLOCK_DICT
, BLOCKDICT_COMP
, RLE
For details, see Encoding types.
Numeric versus integer and floating data types
Numeric data types are exact data types that store values of a specified precision and scale, expressed with a number of digits before and after a decimal point. This contrasts with the Vertica integer and floating data types:

DOUBLE PRECISION (FLOAT) supports ~15 digits, variable exponent, and represents numeric values approximately. It can be less precise than NUMERIC data types.

INTEGER supports ~18 digits, whole numbers only.
The NUMERIC data type is preferred for noninteger constants, because it is always exact. For example:
=> SELECT 1.1 + 2.2 = 3.3;
?column?

t
(1 row)
=> SELECT 1.1::float + 2.2::float = 3.3::float;
?column?

f
(1 row)
Numeric operations
Supported numeric operations include the following:
 Basic math
+ – * /
 Aggregation
SUM
MIN
MAX
COUNT
 Comparison
< <= = <=> <> > >=

NUMERIC divide operates directly on numeric values, without converting to floating point. The result has at least 18 decimal places and is rounded.

NUMERIC mod (including %) operates directly on numeric values, without converting to floating point. The result has the same scale as the numerator and never needs rounding.

Some complex operations used with numeric data types result in an implicit cast to FLOAT. When using SQRT, STDDEV, transcendental functions such as LOG, and TO_CHAR/TO_NUMBER formatting, the result is always FLOAT.
Examples
The following series of commands creates a table that contains a numeric data type and then performs some mathematical operations on the data:
=> CREATE TABLE num1 (id INTEGER, amount NUMERIC(8,2));
Insert some values into the table:
=> INSERT INTO num1 VALUES (1, 123456.78);
Query the table:
=> SELECT * FROM num1;
id  amount
+
1  123456.78
(1 row)
The following example returns the NUMERIC column, amount, from table num1:
=> SELECT amount FROM num1;
amount

123456.78
(1 row)
The following syntax adds one (1) to the amount:
=> SELECT amount+1 AS 'amount' FROM num1;
amount

123457.78
(1 row)
The following syntax multiplies the amount column by 2:
=> SELECT amount*2 AS 'amount' FROM num1;
amount

246913.56
(1 row)
The following syntax returns a negative number for the amount column:
=> SELECT amount FROM num1;
?column?

123456.78
(1 row)
The following syntax returns the absolute value of the amount argument:
=> SELECT ABS(amount) FROM num1;
ABS

123456.78
(1 row)
The following syntax casts the NUMERIC amount as a FLOAT data type:
=> SELECT amount::float FROM num1;
amount

123456.78
(1 row)
See also
Mathematical functions
4  Numeric data type overflow
Vertica does not check for overflow (positive or negative) except in the aggregate function SUM().
Vertica does not check for overflow (positive or negative) except in the aggregate function SUM
()
. If you encounter overflow when using SUM
, use SUM_FLOAT
()
which converts to floating point.
For a detailed discussion of how Vertica handles overflow when you use the functions SUM, SUM_FLOAT, and AVG with numeric data types, see Numeric data type overflow with SUM, SUM_FLOAT, and AVG. The discussion includes directives for turning off silent numeric overflow and setting precision for numeric data types.
Dividing by zero returns an error:
=> SELECT 0/0;
ERROR 3117: Division by zero
=> SELECT 0.0/0;
ERROR 3117: Division by zero
=> SELECT 0 // 0;
ERROR 3117: Division by zero
=> SELECT 200.0/0;
ERROR 3117: Division by zero
=> SELECT 116.43 // 0;
ERROR 3117: Division by zero
Dividing zero as a FLOAT by zero returns NaN:
=> SELECT 0.0::float/0;
?column?

NaN
=> SELECT 0.0::float//0;
?column?

NaN
Dividing a nonzero FLOAT by zero returns Infinity:
=> SELECT 2.0::float/0;
?column?

Infinity
=> SELECT 200.0::float//0;
?column?

Infinity
Add, subtract, and multiply operations ignore overflow. Sum and average operations use 128bit arithmetic internally. SUM
()
reports an error if the final result overflows, suggesting the use of SUM_FLOAT
(INT)
, which converts the 128bit sum to a FLOAT
. For example:
=> CREATE TEMP TABLE t (i INT);
=> INSERT INTO t VALUES (1<<62);
=> INSERT INTO t VALUES (1<<62);
=> INSERT INTO t VALUES (1<<62);
=> INSERT INTO t VALUES (1<<62);
=> INSERT INTO t VALUES (1<<62);
=> SELECT SUM(i) FROM t;
ERROR: sum() overflowed
HINT: try sum_float() instead
=> SELECT SUM_FLOAT(i) FROM t;
SUM_FLOAT

2.30584300921369e+19
5  Numeric data type overflow with SUM, SUM_FLOAT, and AVG
When you use the functions SUM, SUM_FLOAT, and AVG with a NUMERIC data type, be aware that overflow can occur and how Vertica responds to that overflow.
When you use the functions SUM, SUM_FLOAT, and AVG with a NUMERIC data type, be aware that overflow can occur and how Vertica responds to that overflow.
This discussion applies to both the aggregate and analytic functions.
For queries, when using the functions SUM, SUM_FLOAT, and AVG with a NUMERIC data type, Vertica allows for silent overflow if you exceed your specified precision.
Vertica also allows numeric overflow when you use the SUM or SUM_FLOAT functions with LAPs.
Default overflow handling
With NUMERIC data types, Vertica internally works with multiples of 18 digits. If your specified precision is less than 18 (for example, x(12,0)
), Vertica allows for an overflow up to and including the first multiple of 18. In some situations, if you sum a column (SUM(x)
), you can exceed the number of digits Vertica internally reserves for the result. In this case, Vertica allows a silent overflow.
Turning off silent numeric overflow
You can turn off silent numeric overflow and instruct Vertica to implicitly include extra digit places. Specifying extra spaces allows Vertica to consistently return your expected results, even when you exceed the precision specified in your DDL.
You turn off silent numeric overflow by setting the parameter AllowNumericOverflow to 0 (false).
When you set the parameter to 0, Vertica considers the value of a corresponding parameter, NumericSumExtraPrecisionDigits.
The NumericSumExtraPrecisionDigits parameter defaults to 6, meaning that Vertica internally add six places beyond your DDLspecified precision. Adding extra precision digits can allow Vertica to consistently return results that overflow your DDLspecified precision. However, there can be a performance impact for crossing into the second multiple of 18 internally.
An example:

Suppose your DDL specifies 11 (for example, x(11,0)
) and you accept the default of NumericSumExtraPrecisionDigits (6). In this case, Vertica internally stays within the first multiple of 18 digits and no additional performance impact occurs.

Given the same example, if you set NumericSumExtraPrecisionDigits to 10, Vertica internally crosses a threshold into the second multiple of 18. Performancewise, if (hypothetically) the first example is performance “a,” then the second is “2a,” substantially increasing the performance impact. Beyond the second multiple of 18, the performance impact continues to be "2a."
This sample representation shows how Vertica responds internally when you set AllowNumericOverflow
to 0 (false).
Vertica recommends that you turn off silent numeric overflow and set the parameter NumericSumExtraPrecisionDigits if you expect to exceed the precision specified in your DDL. Crossing into the second multiple of 18 can affect performance. Therefore, consider carefully before setting NumericSumExtraPrecisionDigits to a number higher than what you need for returning the SUM of your numeric columns.
Be aware that, if you turn off AllowNumericOverflow, and you exceed the number of extra precision digits set by NumericSumExtraPrecisionDigits, Vertica returns an error.
Impact on live aggregate projections (LAPs)
For LAPs, Vertica also allows silent numeric overflow if your LAP uses the SUM or SUM_FLOAT functions. To turn off silent numeric overflow for LAPs:

Set the parameter AllowNumericOverflow to 0.

Set the parameter NumericSumExtraPrecisionDigits to the number of implicit digits you want. Alternatively, use the default setting of 6.

Drop and recreate your LAPs.
If you turn off silent numeric overflow, be aware of the following scenarios where an overflow causes a roll back or error message. In these examples, AllowNumericOverflow is set to 0 (false), and each LAP uses the SUM or SUM_FLOAT function.
When numeric overflow is off:

A load can roll back upon overflow.
Vertica aggregates data before loading in into a LAP. If you are inserting, copying, or merging data, and an overflow occurs during load as Vertica is aggregating the data, Vertica rolls back the load.

An overflow can occur after load as Vertica sums existing data.
Vertica computes the sum of existing data separately from the computation that it does during data load. If your LAP selects a column using SUM or SUM_FLOAT and an overflow occurs, Vertica produces an error message. This response is similar to the way Vertica produces an error for a query using the SUM or SUM_FLOAT function.

An overflow can occur during mergeout.
Vertica logs a message during mergeout if an overflow occurs as Vertica computes a final sum during the tuple mover operation. If an error occurs, Vertica marks the LAP as outofdate. Vertica no longer runs tuple mover operations with the outofdate LAP.