PostgreSQL database documentation of to_char() – explaining money and string formatting

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
$12,345,678.90
$123,456.78
$0.00
$-12,345.67

On a server set for the Deutsche Mark, FM99G999G990D00L will yield values like
12.345.678,90DM
123.456,78DM
0,00DM
-12.345.678,90DM

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

1 thought on “PostgreSQL database documentation of to_char() – explaining money and string formatting”

Leave a Reply

Your email address will not be published. Required fields are marked *