This section is from the "Practical PostgreSQL" book, by John Worsley and Joshua Drake. Also available from Amazon: Practical PostgreSQL.

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
**

Attribute | Value |
---|---|

Precision | 30 |

Scale | 6 |

Maximum | 999999999999999999999999.999999 |

Minimum | -999999999999999999999999.999999 |

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)

Continue to: