Living in a country, where our currency symbol is the $ x.00, I wasn’t aware of the vast differences between countries and how they deal with currency. France uses , instead of . for decimals. Germany uses a space instead of a comma. The decimal places in some currencies have 3 decimal places, or no decimal places.
Up until PostgreSQL 8.2, money was deprecated. So I stuck to the advice and used numeric and formatted it to my own currency instead, unaware of the issues I created for everyone else in the world. In Postgresql version 8.3, some of its underlying weakness were fixed. And I figured it was time I fixed my currency display once and for all.
If you keep using numeric, you can do proper formatting when you
display the currency. But with the money type you don’t have to worry about formatting anymore.
Changing your schema can be easier sometimes than changing everywhere where you display a column.
Making the move is not as straightforward with changing your schema though, as money and numeric are not interchangeable types.
<code>
-- As per <a href="http://www.postgresql.org/docs/8.2/static/sql-altertable.html">postgresql documentation.</a>
-- Triggers on table must be disabled first, to stop mass data changes.
alter table journal disable trigger user;
-- Defaults must be removed and reset.
alter table journal alter warnonowe drop default;
-- You need to cast to text first, then to money. Numeric cast to money does not work.
alter table journal alter warnonowe type money using warnonowe::text::money
-- check default value, 0 is not allowable, has to be '0'
alter table journal alter warnonowe set default '0';
-- re-enable trigger;
alter table journal enable trigger user;
</code>
Example of realization is available here:
https://aussielowdepositcasino.com/play-wheres-the-gold-slot-machine/
