Deprecated: The money type is deprecated. Use numeric or decimal instead, in combination with the
to_charfunction. The money type may become a locale-aware layer over the numeric type in a future release.
The money type stores U.S.-style currency with fixed decimal point representation. If PostgreSQL is compiled with locale support then the money type uses locale-specific output formatting.
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 in the latter form.
I would strongly recommend against using the money type because very few operators support it. Forget doing any manipulation beyond arithmatic.
Use numeric instead and slap a dollar sign on it with to_char after you do whatever mathmatics you need.
In order to migrate Currency data in Access97 into PostgreSQL, I had to use the NUMERIC data type. The 'Money' data type did not work during the ODBC translation and prevented data from posting to the postgres table. This is just to confirm the recommendation to use the NUMERIC for all currency data.
Thanks for the previously mentioned tip.
If you have data in the 'money' type and want to get it back out again, you'll find that Postgres won't let you cast to numeric, to decimal, to text, or really do anything useful at all. You might find this handy:
CREATE OR REPLACE FUNCTION moneytonumeric(money) RETURNS numeric LANGUAGE sql IMMUTABLE AS '
((((get_byte(cash_send($1),0) * 256) + get_byte(cash_send($1),1)) * 256) + get_byte(cash_send($1),2)) * 256 + get_byte(cash_send($1),3)
This uses cash_send() to package up a money value into an array of bytes, and then uses get_byte to reconstruct the original integer. This is likely endian-specific (if you're running on a non-Intel platform, change 0,1,2,3 to 3,2,1,0) and is likely version-specific as well. However, I found that this works better than the other options I've explored.