On 28 March 2013 13:52, Shaun Thomas <sthomas@xxxxxxxxxxxxxxxx> wrote:
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).
On 03/28/2013 07:43 AM, Gavan Schneider wrote:You're not the only one. In the financial industry, we can't even use the money type for a few reasons:
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.
[... 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.
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.
