PostgreSQL 9.0.23 Documentation | ||||
---|---|---|---|---|
Prev | Up | Chapter 8. Data Types | Next |
The money type stores a currency amount with a fixed fractional precision; see Table 8-3. The fractional precision is determined by the database's lc_monetary setting. 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. Non-quoted numeric values can be converted to money by casting the numeric value to text and then money, for example:
SELECT 1234::text::money;
There is no simple way of doing the reverse in a
locale-independent manner, namely casting a money value to a numeric type. If you know the
currency symbol and thousands separator you can use regexp_replace()
:
SELECT regexp_replace('52093.89'::money::text, '[$,]', '', 'g')::numeric;
Since the output of this data type is locale-sensitive, it might not work to load money data into a database that has a different setting of lc_monetary. To avoid problems, before restoring a dump into a new database make sure lc_monetary has the same or equivalent value as in the database that was dumped.