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