The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference
by The PostgreSQL Global Development Group Paperback (6"x9"), 454 pages ISBN 9781906966041 RRP £14.95 ($19.95) Sales of this book support the PostgreSQL project! Get a printed copy>>> |

### 6.1.2 Arbitrary Precision Numbers

The type `numeric`

can store numbers with up to 1000
digits of precision and perform calculations exactly. It is
especially recommended for storing monetary amounts and other
quantities where exactness is required. However, arithmetic on
`numeric`

values is very slow compared to the integer
types, or to the floating-point types described in the next section.

We use the following terms below: The
*scale* of a `numeric`

is the
count of decimal digits in the fractional part, to the right of
the decimal point. The *precision* of a
`numeric`

is the total count of significant digits in
the whole number, that is, the number of digits to both sides of
the decimal point. So the number 23.5141 has a precision of 6
and a scale of 4. Integers can be considered to have a scale of
zero.

Both the maximum precision and the maximum scale of a
`numeric`

column can be
configured. To declare a column of type `numeric`

use
the syntax:

NUMERIC(precision, scale)

The precision must be positive, the scale zero or positive. Alternatively:

NUMERIC(precision)

selects a scale of 0. Specifying:

NUMERIC

without any precision or scale creates a column in which numeric
values of any precision and scale can be stored, up to the
implementation limit on precision. A column of this kind will
not coerce input values to any particular scale, whereas
`numeric`

columns with a declared scale will coerce
input values to that scale. (The SQL standard
requires a default scale of 0, i.e., coercion to integer
precision. We find this a bit useless. If you're concerned
about portability, always specify the precision and scale
explicitly.)

If the scale of a value to be stored is greater than the declared scale of the column, the system will round the value to the specified number of fractional digits. Then, if the number of digits to the left of the decimal point exceeds the declared precision minus the declared scale, an error is raised.

Numeric values are physically stored without any extra leading or
trailing zeroes. Thus, the declared precision and scale of a column
are maximums, not fixed allocations. (In this sense the `numeric`

type is more akin to `varchar(`

than to `n`)`char(`

.) The actual storage
requirement is two bytes for each group of four decimal digits,
plus five to eight bytes overhead.
`n`)

In addition to ordinary numeric values, the `numeric`

type allows the special value `NaN`

, meaning
“not-a-number”. Any operation on `NaN`

yields another `NaN`

. When writing this value
as a constant in an SQL command, you must put quotes around it,
for example `UPDATE table SET x = 'NaN'`

. On input,
the string `NaN`

is recognized in a case-insensitive manner.

Note:In most implementations of the “not-a-number” concept,`NaN`

is not considered equal to any other numeric value (including`NaN`

). In order to allow`numeric`

values to be sorted and used in tree-based indexes, PostgreSQL treats`NaN`

values as equal, and greater than all non-`NaN`

values.

The types `decimal`

and `numeric`

are
equivalent. Both types are part of the SQL
standard.

ISBN 9781906966041 | The PostgreSQL 9.0 Reference Manual - Volume 1A - SQL Language Reference | See the print edition |