- publishing free software manuals
 PostgreSQL Reference Manual - Volume 1 - SQL Language Reference by The PostgreSQL Global Development GroupPaperback (6"x9"), 716 pagesISBN 0954612027RRP £32.00 (\$49.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.

In what follows we use these terms: 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(n)` than to `char(n)`.) The actual storage requirement is two bytes for each group of four decimal digits, plus eight bytes overhead.

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 a 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.

The types `decimal` and `numeric` are equivalent. Both types are part of the SQL standard.

 ISBN 0954612027 PostgreSQL Reference Manual - Volume 1 - SQL Language Reference See the print edition