8.2. Monetary Types#
Themoney type stores a currency amount with a fixed fractional precision; seeTable 8.3. The fractional precision is determined by the database'slc_monetary setting. The range shown in the table assumes there are two fractional digits. Input is accepted in a variety of formats, including integer and floating-point literals, as well as typical currency formatting, such as'$1,000.00'. Output is generally in the latter form but depends on the locale.
Table 8.3. Monetary Types
| Name | Storage Size | Description | Range |
|---|---|---|---|
money | 8 bytes | currency amount | -92233720368547758.08 to +92233720368547758.07 |
Since the output of this data type is locale-sensitive, it might not work to loadmoney data into a database that has a different setting oflc_monetary. To avoid problems, before restoring a dump into a new database make surelc_monetary has the same or equivalent value as in the database that was dumped.
Values of thenumeric,int, andbigint data types can be cast tomoney. Conversion from thereal anddouble precision data types can be done by casting tonumeric first, for example:
SELECT '12.34'::float8::numeric::money;
However, this is not recommended. Floating point numbers should not be used to handle money due to the potential for rounding errors.
Amoney value can be cast tonumeric without loss of precision. Conversion to other types could potentially lose precision, and must also be done in two stages:
SELECT '52093.89'::money::numeric::float8;
Division of amoney value by an integer value is performed with truncation of the fractional part towards zero. To get a rounded result, divide by a floating-point value, or cast themoney value tonumeric before dividing and back tomoney afterwards. (The latter is preferable to avoid risking precision loss.) When amoney value is divided by anothermoney value, the result isdouble precision (i.e., a pure number, not money); the currency units cancel each other out in the division.