I think MONEY is a great datatype, at least in theory.
It's stored as a 64 bit binary integer with an implied, fixed decimal
scale. This means that storage is conserved and operations are as fast
and exact as possible (to the implied decimal scale.)
Unfortunately it has a couple of significant drawbacks.
1. You can't specify the scale on a column-by-column basis, such as
MONEY(2) and MONEY(4), which would be useful in many databases[1];
instead, the scale is a configuration setting (lc_monetary) that is
only used when converting MONEY values to/from their string
representation or other datatypes. Inside the DB it's just a bigint.
2. By default it outputs its values in the US locale format: $1,200.00
which looks kind of silly (or out of place) as a data representation
format between database and application layers.
Is there any value I can assign to lc_monetary, or any other
configuration variable, that will make MONEY display its values
without dollar signs and thousand separators? (like NUMERIC would.) I
understand I can just cast each MONEY column to ::numeric to get that
representation format, or alternatively strip the dollar and commas on
the application side, but it would be easier to just specify it as a
configuration variable. I hoped the value 'C' would give a
locale-agnostic format (as far as such a thing can exist) but it
defaults to US locale as well.
Finally, I would like to throw out there the idea of a MONEY(s) or
maybe FIXED(s) type, to represent numbers as 64 bit binary integers
with an implied decimal scale that is specific to each column (and no
dollars or commas on output.) Unfortunately I have no idea how big of
a change this would require in the codebase. It's probably a
completely new datatype. Maybe there is already such a type as an
extension, under a different name?
-Tobia
[1] for example, many databases store the price of a single item, such
as a single nut and bolt, as a 1e-4 or 1e-6 fraction of the local
currency, but store the totals of invoices to the law-mandated
precision, say 1e-2.
