The money type is an automatically translated numeric type that formats to and from US-style currency notation, and plain numeric values. As of the writing of this book, the money type is deprecated, and is discouraged from being actively used. It is only presented here as it is still a functional data type, and may be in use on existing PostgreSQL systems.

The suggested alternative to the money type is the numeric type, with a scale of 2 to represent coin values, and a precision large enough to store the largest necessary monetary value (including two digits for the coin precision). Formatting similar to that of the money type can be achieved with the to_char function, as shown in Example 3-25 . This example demonstrates the text concatentation operator covered in the section called Character and Text Operators in Chapter 4 , and the ltrim() text formatting function, described in Chapter 4 .

Example 3-25. A numeric Alternative to money

booktown=# INSERT INTO money_example VALUES ('$12.24', 12.24);
INSERT 3391095 1
booktown=# SELECT * FROM money_example;
 money_cash | numeric_cash
------------+--------------
     $12.24 |        12.24
(1 row)
 
booktown=# SELECT money_cash, 
booktown-#        '$' || ltrim(to_char(numeric_cash, '9999.99')) 
booktown-#        AS numeric_cashified 
booktown-#        FROM money_example;
 money_cash | numeric_cashified
------------+-------------------
     $12.24 | $12.24
(1 row)