Re: Money casting too liberal?

On 28 March 2013 13:52, Shaun Thomas <sthomas(at)optionshouse(dot)com> wrote:

> On 03/28/2013 07:43 AM, Gavan Schneider wrote:
>
> Personally I have ignored the money type in favour of numeric. Money
>> seemed to do too much behind the scenes for my taste, but, that's me
>> being lazy as well, I haven't spend much time trying to understand its
>> features.
>>
>
> You're not the only one. In the financial industry, we can't even use the
> money type for a few reasons:
>
> [... snip ... ]
>

Speaking as a another finance/trading industry techie who works with
various kinds of price data, I also find the money type interesting but
useless. I am interested in scaled integers in general though, be they of
fixed scale (per column, part of the type) or of floating scale (floating
point decimal). I have run into those all over the place in software and
protocols. They can be stored and computed more efficiently than the more
general variable sized BCD string system where scale and precision are more
like check constraints than limits of representation allowing for fixed
size bitfields

For floating point decimal, IEEE 754 2008 decimal32, decimal64, decimal128
types would make interesting additions (the scale travels with each
number.. it's essentially a bitfield of sign + exponent/scale + significand
which is efficient for software implements, or an isomorphic BCD-like fixed
size encoding which is used by IBM's POWER DFP hardware). But that can be
implemented as custom types outside core PostgreSQL (I've done some initial
experimentation with this, defining a type DECIMAL64, and not encountered
any obstacles, using IBM decNumber, which is available under the liberal
ICU license or the GPL license, and is used by many projects; there is also
an Intel library with a BSD license IIRC).

For fixed point decimal, a new scaled integer type with fixed scale and
precision could be made that uses different representation depending on the
parameters, much like the way Common LIsp implementations use fixnums based
on word size while possible, and fall back to arbitrary sized systems if
needed. That would of course be implementable outside core too.

Even the built-in NUMERIC could in theory use multiple encodings, whenever
the scale and precision are provided, since it can work out whether they
are within the limits that are implementable with different binary
representations (in other words, when you ask for NUMERIC(*, 2), do what
MONEY for US locales does, otherwise fall back to the more general case).
But that would change the rules about when rewrites are required if you
change scale/precision, so wouldn't be reasonable.