I use decimal(10,2) for whole cents, and decimal(12,6) for sub-cents. Single currency only. I didn't know there was a money type originally, but it wouldn't be usable for me anyway without the ability to specify the scale and precision.
It is worth noting that decimal is an alias for numeric in Postgres. For that reason you will have less confusion if you use numeric instead.
I recommend considering passing values to the database as "decimal '1.23'" rather than bare numeric literals, just so there's no chance of the value being interpreted as a float at any stage by postgres. Maybe that's being too paranoid but that's a good idea when it comes to money. :-)
I don't think the type designation buys you anything. unless it is a part of an arithmetic expression The single quotes do and cannot be omitted here.
So I think there is a difference between ('1.23' + 1)::numeric and '1.23'::numeric + 1 but there is also a difference between 1.23::numeric + 1 and '1.23'::numeric + 1
But there is no reason to add the cast when doing something like an insert of a single value.
Perhaps the incorporation of currency would make a new money type interesting. Currencies probably don't change as often as timezones but there would probably still be some ongoing need for updates.
The existing money type has another problem in that the currency it is attached to is taken from the current locale. So if you change your locale settings you can change a value from, say, 100 IDR to 100 EUR at least for display purposes.
I have some thoughts about how to do a multi-currency type but I am not actually sure you get anything by tying the data together instead of having it in separate columns.
cheers, raf
--
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.
