The numeric (also known as decimal ) type is a specially designed numeric data type which can represent arbitrarily large and precise values, within a fixed definition. When you create a table with a column of type numeric , you may specify in parentheses two values: the precision , and the scale .
The precision is the maximum number of sequential digits that the numeric value may hold (including digits to the right of the decimal point), while the scale describes how many of those digits of precision are to be to the right of the decimal point. If left unspecified, the precision will default to 30, and the scale will default to 6.
Table 3-13. Default Numeric Type
Unlike the floating point data types, however, the numeric type will cause an overflow error if digits are specified outside of its precision range. You may somewhat safely specify non-zero digits which are smaller than the scale of the numeric definition, but only provided that the entirety of the numeric input fits within the maximum precision defined (though the value may be rounded).
For example, in a numeric(11,6) column, you may safely insert the value 9.9999999 with two digits too many to the right (though the value is rounded up to 10.000000 ). However, an attempt to insert the value 99999.99999999 will fail, as shown in Example 3-24 .
This problem can be explicitly avoided by using the trunc() numeric truncating function, which is illustrated in Example 3-24 . Used consistently, it also can keep unwanted rounding from happening by forcing an appropriate scale on the data before attempting to insert.
Example 3-24. Avoiding Overflow Errors
booktown=# INSERT INTO numbers VALUES (9.99999999); INSERT 3390697 1 booktown=# SELECT * FROM numbers; number -------------- 10.000000 (1 row) booktown=# INSERT INTO numbers VALUES (99999.99999999); ERROR: overflow on numeric ABS(value) >= 10^5 for field with precision 11 scale 6 booktown=# INSERT INTO numbers VALUES (trunc(99999.99999999, 6)); INSERT 3390698 1 booktown=# SELECT * FROM numbers; number -------------- 10.000000 99999.999999 (2 rows) booktown=# INSERT INTO numbers VALUES (trunc(9.99999999, 6)); INSERT 3390699 1 booktown=# SELECT * FROM numbers; number -------------- 10.000000 99999.999999 9.999999 (3 rows)