Numeric Data Type Overflow
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 non-zero 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 128-bit arithmetic internally. SUM
()
reports an error if the final result overflows, suggesting the use of SUM_FLOAT
(INT)
, which converts the 128-bit sum to a FLOAT8
. 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