NUMERIC
Numeric data types store fixed-point numeric data. For example, a value of $123.45 can be stored in a NUMERIC(5,2)
field.
Syntax
numeric‑type [ ( precision[, scale] ) ]
Parameters
numeric‑type |
One of the following:
|
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. |
Maximum Significant Digits
The maximum number of significant digits (digits to the left of the decimal point) is equal to precision minus scale. For example, NUMERIC(9, 6)
allows three significant digits. In this case, if you insert a value that has more than three significant digits, an error occurs.
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
|
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 thanNUMERIC
data types.INTEGER
supports ~18 digits, whole numbers only.
The NUMERIC
data type is preferred for non-integer 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 usingSQRT
,STDDEV
, transcendental functions such asLOG
, andTO_CHAR/TO_NUMBER
formatting, the result is alwaysFLOAT
.
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)