The postgres documentation at http://www.postgresql.org/docs/8.3/interactive/functions-formatting.html is a bit lacking on examples as to how to format money.
For instance, the FM template pattern applies to both Date/Time and numeric, so Table 9-23 needs to have FM in it as well.
Additionally, the examples in table 9-24 need to have a disclaimer stating that the examples assume the server is set for Deutsche Marks, whose currency symbol is ‘DM’ and is also set to use a comma as a decimal separator and a period as a thousands separator (Example: 4.567,89 DM) . For the US Dollar ($) it is a comma as a thousands separator and a period as a decimal separator (Example: $4,567.89).
Additionally, Table 9-24 would benefit from the following example:
TO_CHAR(123456789, ‘LFM99G999G990D00’) Yields $1,234,567.89
On a server set for the US Dollar, LFM99G999G990D00 will yield values like
On a server set for the Deutsche Mark, FM99G999G990D00L will yield values like
Notice that I moved the L to the end since Deutsche Marks should be marked that way according to Wikipedia.
The example in Table 9-24 is technically incorrect for to_char(485, ‘L999’)
NOTE: this will “overflow” when you hit 100 million; so something like 555,666,777.88 would end up showing up as $##,###,###.## which is quite annoying.
To fix that, add more 9’s and G’s
An example in a SELECT statement
SELECT TO_CHAR(total_amount, ‘LFM999G999G999G990D00’) as total_amount FROM invoices