Table Column Types

Part:   1  2   3 

(Continued from previous part...)

Table Column Types for Exact Numbers

There 4 types of table columns to store exact numbers: NUMERIC, DECIMAL, INTEGER and SMALLINT.

"NUMERIC(precision, scale)" - Exact numeric value with specified scale and specified precision.

"DECIMAL(precision, scale) or DEC(precision, scale)" - Exact numeric value with specified scale and specified precision or higher.

"INTEGER" - Exact number value with scale of 0. Precision is defined by the implementation.

"SMALLINT" - Exact number value with scale of 0. Precision is defined by the implementation and should be lower than INTEGER.

Examples of exact number column types, ExactNumberColumns.sql:

-- ExactNumberColumns.sql
-- Copyright (c) 1999 by Dr. Herong Yang
--
CREATE TABLE ExactNumber (
   Line VARCHAR(8),
   Price NUMERIC(8,2), -- (precision, scale)
   Rate DECIMAL(8,2), 
   Population INTEGER,
   Quantity SMALLINT
   );
INSERT INTO ExactNumber VALUES (
   '1',
   1.11,
   2.22, 
   33,
   4
   );
INSERT INTO ExactNumber VALUES (
   '2',
   11.111,
   22.222, 
   3333.3,
   44.4
   );
INSERT INTO ExactNumber VALUES (
   '3',
   1111.1,
   2222.2, 
   333333,
   444
   );
INSERT INTO ExactNumber VALUES (
   '4',
   111111.119,
   222222.229, 
   33333333,
   4444
   );
INSERT INTO ExactNumber VALUES (
   '5',
   11111111.119,
   22222222.229, 
   3333333333,
   44444
   );
SELECT * FROM ExactNumber;
DROP TABLE ExactNumber;

Output:

Line    Price   Rate    Population      Quantity
1       1.11    2.22    33      4
2       11.11   22.22   3333    44
3       1111.10 2222.20 333333  444
4       111111.12       222222.23       33333333        4444
5       9999999.99      9999999.99      2147483647      32767

Note that:

• Line 2 tells us that extra fractional digits will be rounded down or up.
• Line 3 tells us that fractional digits will be padded to match the specified scale.
• Line 5 tells us that when a number reaches the specified precision, it will be replaced with the maximum value of the specified precision.
• I don't see any differences between NUMERIC and DECIMAL.

Table Column Types for Approximate Numbers

There 3 types of table columns to store approximate numbers: FLOAT, REAL and DOUBLE PRECISION.

"FLOAT(precision)" - Approximate numeric value with specified precision or higher. No scale is specified for FLOAT.

"REAL" - Approximate numeric value with no scale. Precision is defined by the implementation.

"DOUBLE PRECISION" - Approximate numeric value with no scale. Precision is defined by the implementation and should be higher than REAL.

(Continued on next part...)

Part:   1  2   3