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

PostgreSQL 9.0 Reference Manual Volume 1A Buy the book here! >>> support free software

6.2 Monetary Types

The money type stores a currency amount with a fixed fractional precision; see Table 6-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.