NUMERIC

Numeric data types store fixed point numeric data. For example, a money value of $123.45 can be stored in a NUMERIC(5,2) field.

Syntax

NUMERIC | DECIMAL | NUMBER | MONEY [ ( precision [ , scale ] ) ]

Parameters

precision

scale

Notes

• 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.
• Numeric data types that have a precision of 18 or less have similar performance characteristics as INTEGER, regardless of the scale. When possible, Vertica recommends using a precision <= 18.

• NUMERIC, DECIMAL, NUMBER, and MONEY are all synonyms that return NUMERIC data. The four numeric data types differ in their default precision and scale values:

  Type	Precision	Scale
  NUMERIC	37	15
  DECIMAL	37	15
  NUMBER	38	0
  MONEY	18	4

• 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:

  The DOUBLE PRECISION (FLOAT) type supports ~15 digits, variable exponent, and represents numeric values approximately.

  The INTEGER type supports ~18 digits, whole numbers only.

• The approximate numeric data type, DOUBLE PRECISION, uses floating points and may be less precise than NUMERIC data types.

• Supported numeric operations include the following:

  Basic math: +, –, *, /

  Aggregation: SUM, MIN, MAX, COUNT

  Comparison operators: <, <=, =, <=>, <>, >, >=

• 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.
• NULL appears first (smallest) in ascending order.
• COPY accepts a DECIMAL data type with a decimal point ('.'), prefixed by – or +(optional).

• 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)
  

• Performance of the NUMERIC data type has been fine tuned for the common case of 18 digits of precision.
• Some of the more 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.
• For valid encoding types for numeric data type columns, see Encoding Types.

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)