Money casting too liberal?
In contrast to certain other open-source databases, PostgreSQL leans
toward protecting data from surprises and erroneous input, i.e.
rejecting a date of 2013-02-31 instead of arbitrarily assigning a date
of 2013-03-03. Similar "throw error" instead of "take a guess"
philosophy applies to numeric and string operations as well. It's an
approach I appreciate.
But it appears that the philosophy does not extend to the "money" type.
Although there are certain checks including no alpha, '$' and '-', if
present, must be in the first two characters of the string and commas
can't be at the end. Otherwise the casting is fairly liberal. Commas,
for instance, can appear nearly anywhere including after the decimal point:
select ',123,456,,7,8.1,0,9'::money;
money
----------------
$12,345,678.11
Somewhat more worrisome is the fact that it automatically rounds input
(away from zero) to fit.
select '123.456789'::money;
money
---------
$123.46
select '$-123.456789'::money;
money
----------
-$123.46
Thoughts? Is this the "no surprises" way that money input should behave?
Cheers,
Steve
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Steve Crawford wrote
select ',123,456,,7,8.1,0,9'::money;
money
----------------
$12,345,678.11
As an end-user it would seem since a comma (or whatever the locale defines
as a group separator) carries no significant information - it is purely
aesthetic - that ignoring all commas during input conversion is a reasonable
behavior. The placement of the commas is mere convention and not formally
encoded in the locale. While for money the point is usually moot a long
sequence fractional numbers would benefit equally from having a group
separator just as the long sequence of whole numbers does traditionally.
Steve Crawford wrote
Somewhat more worrisome is the fact that it automatically rounds input
(away from zero) to fit.select '123.456789'::money;
money
---------
$123.46
This too seems to be useful since, for instance, if you end up dividing a
money by a unit - to get a per-unit value - the end result should be in the
same currency and would have to be rounded to the maximum precision of the
currency involved.
Given that neither behavior is overtly wrong if during data entry you feel a
more stringent input string is required you will need to use a regular
expression to assert that constraint.
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Money-casting-too-liberal-tp5749919p5749933.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 27/3/13 at 9:12 AM, Steve Crawford wrote:
In contrast to certain other open-source databases, PostgreSQL leans
toward protecting data from surprises ...
And long may this continue.
But it appears that the philosophy does not extend to the "money"
type. ...select ',123,456,,7,8.1,0,9'::money;
money
----------------
$12,345,678.11
In general terms I would hate for such probable garbage to
appear as "legitimate" data in the dB.
Somewhat more worrisome is the fact that it automatically rounds input (away from zero) to fit.
select '123.456789'::money;
money
---------
$123.46select '$-123.456789'::money;
money
----------
-$123.46Thoughts? Is this the "no surprises" way that money input should behave?
I would defer to a CPA on the correct conventions for rounding.
However I have a vague notion there are circumstances when
rounding is always up, always down and (only sometimes) to the
nearest. If the money type is meant to be serious then these
conventions need to be followed/settable on a column by column
basis. And money is done in whole dollars, thousands of dollars,
and fractional cents according to the situation, i.e., not just
two decimal places... another setting.
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.
Regards
Gavan Schneider
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Thu, 28 Mar 2013 23:43:23 +1100
Gavan Schneider <pg-gts@snkmail.com> wrote:
But it appears that the philosophy does not extend to the "money"
type. ...
As the original author of the money type I guess I should weigh in.
select ',123,456,,7,8.1,0,9'::money;
money
----------------
$12,345,678.11
It certainly doesn't accept that by design. I just never thought about
such input. If you put garbage in anything can happen including
acceptance. If this is an issue I guess we need to look for such things
and reject it. Just a SMOP.
I would defer to a CPA on the correct conventions for rounding.
However I have a vague notion there are circumstances when
rounding is always up, always down and (only sometimes) to the
nearest. If the money type is meant to be serious then these
conventions need to be followed/settable on a column by column
Possible. Generally I handle these issues in code because it is
sometimes hard to nail down exact requirements that fit all. I also
tend to use money only in situations where the exact dollars and cents
is already known or is dealt with in code.
basis. And money is done in whole dollars, thousands of dollars,
and fractional cents according to the situation, i.e., not just
two decimal places... another setting.
I would like to see the type handle other situations such as foreign
(to me) currency, etc. I suppose a positional parameter and a currency
string setting would handle most of those issues. Technically, the
money type is a cents type. Everything is stored as the number of
cents. Formatting it as dollars and cents is a convenience added by
the I/O functions.
Personally I have ignored the money type in favour of numeric.
Even as the author I sometimes go with numeric but there is a place for
the type. If you are working with simple dollars and cents quantities
and you need to do lots of calculations on them, the money type can be
a great performance boost. The big win that money brings is that
everything is stored as an int. That means that you don't need to
convert data in the database to a machine representation before
summing, averaging, etc. The machine can generally work on the data as
it comes out of the DB.
--
D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.
IM: darcy@Vex.Net, VOIP: sip:darcy@Vex.Net--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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:
1. It's very common for values to have fractional amounts in the
selected currency. Just look at gas stations... they could never use the
Money type in the US thanks to the "3.989" pricing they commonly employ.
2. You can't use the Money type for non-local currencies. Our database
may store transactions in several base currencies. Sure, the smart thing
would be to save the exchange rate at the time of the transaction and
store the local value and the rate, but then you'd have to reverse that
calculation to get the original value, and without decimals, that
conversion would be wrong in most cases.
So we use numeric. It's the only thing with the guaranteed precision we
need, and prettying up the display is easy to do client-side. We're
extremely happy to see the recent improvements in numeric performance
that seem to be coming in 9.3. :)
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 27/3/13 at 9:12 AM, Steve Crawford wrote:
Thoughts? Is this the "no surprises" way that money input should behave?
I took a quick look at cash_in(), which is what's being complained of
here (not really casting). There are several things that seem like
they could possibly stand to be tightened up:
1. it allows parens to indicate "negative", eg (1234.56), but it's not
very anal about insisting that a paren appear on the right iff there's
one on the left, nor about the exact position of the trailing paren.
2. it allows thousands separators to the right of the decimal point,
but only as long as it's still looking for digits, viz:
regression=# select '123.4567'::money;
money
---------
$123.46
(1 row)
regression=# select '123.45,67'::money;
money
---------
$123.46
(1 row)
regression=# select '123.456,7'::money;
ERROR: invalid input syntax for type money: "123.456,7"
LINE 1: select '123.456,7'::money;
^
3. it is not picky about where you put thousands separators to the left
of the decimal point.
Of these I think #2 is a flat-out bug: the digit collection loop should
reject thousands seps once it's found a decimal point. #1 is a shortcut
acknowledged in the code comments, and while it probably is worth
fixing, I'm finding it hard to get really excited about it. I think the
debatable point is whether we want to tighten up #3 and if so how much.
We surely do not want to insist that thousands separators are required.
I think that different locales may space them differently --- at least,
cash_out treats the spacing as configurable --- but do we want to
zreject, say, commas every three places if the locale specifies four?
I think that might be a little too picky. On the whole I'm okay with
treating them as noise as long as they're to the left of the decimal.
Gavan Schneider <pg-gts@snkmail.com> writes:
I would defer to a CPA on the correct conventions for rounding.
However I have a vague notion there are circumstances when
rounding is always up, always down and (only sometimes) to the
nearest. If the money type is meant to be serious then these
conventions need to be followed/settable on a column by column
basis. And money is done in whole dollars, thousands of dollars,
and fractional cents according to the situation, i.e., not just
two decimal places... another setting.
If you need that kind of control over rounding you need to be doing it
in your calculation code. Expecting an I/O function to provide it
is doomed to failure.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 29/03/13 02:28, D'Arcy J.M. Cain wrote:
On Thu, 28 Mar 2013 23:43:23 +1100
Gavan Schneider <pg-gts@snkmail.com> wrote:But it appears that the philosophy does not extend to the "money"
type. ...As the original author of the money type I guess I should weigh in.
select ',123,456,,7,8.1,0,9'::money;
money
----------------
$12,345,678.11It certainly doesn't accept that by design. I just never thought about
such input. If you put garbage in anything can happen including
acceptance. If this is an issue I guess we need to look for such things
and reject it. Just a SMOP.I would defer to a CPA on the correct conventions for rounding.
However I have a vague notion there are circumstances when
rounding is always up, always down and (only sometimes) to the
nearest. If the money type is meant to be serious then these
conventions need to be followed/settable on a column by columnPossible. Generally I handle these issues in code because it is
sometimes hard to nail down exact requirements that fit all. I also
tend to use money only in situations where the exact dollars and cents
is already known or is dealt with in code.basis. And money is done in whole dollars, thousands of dollars,
and fractional cents according to the situation, i.e., not just
two decimal places... another setting.I would like to see the type handle other situations such as foreign
(to me) currency, etc. I suppose a positional parameter and a currency
string setting would handle most of those issues. Technically, the
money type is a cents type. Everything is stored as the number of
cents. Formatting it as dollars and cents is a convenience added by
the I/O functions.Personally I have ignored the money type in favour of numeric.
Even as the author I sometimes go with numeric but there is a place for
the type. If you are working with simple dollars and cents quantities
and you need to do lots of calculations on them, the money type can be
a great performance boost. The big win that money brings is that
everything is stored as an int. That means that you don't need to
convert data in the database to a machine representation before
summing, averaging, etc. The machine can generally work on the data as
it comes out of the DB.
I am (now) primarily a Java developer (in my bad past I have done
FORTRAN, COBOL, & other languages ).
I use PostgreSQL in 2 situations:
1. To create a 'concrete sketch' of a sub set of a data model to
explore how to represent things
2. To create an actual production database.
In either case, I want to represent data and constraints in ways that
accurately modelthe data structures, and to provide 'implemented
documentation'. I think that the more semantics and constraints can be
represented in the database the better- for 2 reasons: the declarative
style is eaiser to follow than looking at program code, and it applies
to _ALL_ accesses to the database (so it has more complete coverage and
is hence more reliable)
So I would like a money type that I can use in all appropriate situations.
Cheers,
Gavin
On 2013-03-28, D'Arcy J.M. Cain <darcy@druid.net> wrote:
I would like to see the type handle other situations such as foreign
(to me) currency, etc. I suppose a positional parameter and a currency
string setting would handle most of those issues. Technically, the
money type is a cents type. Everything is stored as the number of
cents. Formatting it as dollars and cents is a convenience added by
the I/O functions.
it actually does that, if you have the locale installed you can set
LC_MONETARY to Japan and get no decimals and a Yen symbol
or to UAE and get three decimals and their currency symbol.
Even as the author I sometimes go with numeric but there is a place for
the type. If you are working with simple dollars and cents quantities
and you need to do lots of calculations on them, the money type can be
a great performance boost. The big win that money brings is that
everything is stored as an int. That means that you don't need to
convert data in the database to a machine representation before
summing, averaging, etc. The machine can generally work on the data as
it comes out of the DB.
it'd be nice to be able to do a money * numeric operation
currently multiplication is done in floating point and this can give
unexpected results.
-- D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner. IM: darcy@Vex.Net, VOIP: sip:darcy@Vex.Net
--
⚂⚃ 100% natural
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 28 Mar 2013 20:50:42 GMT
Jasen Betts <jasen@xnet.co.nz> wrote:
it actually does that, if you have the locale installed you can set
LC_MONETARY to Japan and get no decimals and a Yen symbol
or to UAE and get three decimals and their currency symbol.
Must have been added by someone else after I worked on it. I thought
about that issue but felt that that was the wrong solution. The
problem is that the same data displays differently depending on who
runs the query.
I would have rather made that part of the column definition similar to
how we create timestamps with or without timezones. If a column is
tracking Yen it should always be Yen. Y10,000 should never display as
$100.00 just because the locale changes.
--
D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 788 2246 (DoD#0082) (eNTP) | what's for dinner.
IM: darcy@Vex.Net, VOIP: sip:darcy@Vex.Net--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 3/28/2013 2:13 PM, D'Arcy J.M. Cain wrote:
I would have rather made that part of the column definition similar to
how we create timestamps with or without timezones. If a column is
tracking Yen it should always be Yen. Y10,000 should never display as
$100.00 just because the locale changes.
or to another extreme, part of the data, such that different rows could
have different monetary units. (eg, money is implemented as a pair
(currency,amount). eeek, then you'd need exchange rate tables and
such. hahahahaha, what a nightmare.
--
john r pierce 37N 122W
somewhere on the middle of the left coast
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 03/28/2013 04:36 PM, John R Pierce wrote:
or to another extreme, part of the data, such that different rows could
have different monetary units. (eg, money is implemented as a pair
(currency,amount). eeek, then you'd need exchange rate tables and
such. hahahahaha, what a nightmare.
Naw. Only if you wanted to convert them. I personally wonder why it
wasn't implemented this way to begin with. Like TSTZ, with a MONEY type,
the currency is the TZ segment. XXX amount in YYY encoding, with the
default being the currency of the locale if not otherwise specified. It
would still be useless for calculations in applications requiring more
significant figures, but would make more sense than the currently
magically morphing value it is now.
"Hey, we just shipped a DB server to Japan, and now all of the monetary
values are wrong. WTF!"
Yeah... no. :)
--
Shaun Thomas
OptionsHouse | 141 W. Jackson Blvd. | Suite 500 | Chicago IL, 60604
312-676-8870
sthomas@optionshouse.com
______________________________________________
See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 29/03/13 10:13, D'Arcy J.M. Cain wrote:
On 28 Mar 2013 20:50:42 GMT
Jasen Betts <jasen@xnet.co.nz> wrote:it actually does that, if you have the locale installed you can set
LC_MONETARY to Japan and get no decimals and a Yen symbol
or to UAE and get three decimals and their currency symbol.Must have been added by someone else after I worked on it. I thought
about that issue but felt that that was the wrong solution. The
problem is that the same data displays differently depending on who
runs the query.I would have rather made that part of the column definition similar to
how we create timestamps with or without timezones. If a column is
tracking Yen it should always be Yen. Y10,000 should never display as
$100.00 just because the locale changes.
Eeeks!
I agree...
Hmm... This should optionally apply to time. e.g.
time_i_got_up_in_the_morning should reflect the time zone where I got up
- if I got up at 8am NZ time then this should be displayed, not 12pm (12
noon) to someone in Los Angeles or 3am in Tokyo! (have a 'localtime'
data type?- possibly add the timezone code if displayed in a different
time zone.)
Cheers,
Gavin
On 2013-03-28, Gavin Flower <GavinFlower@archidevsys.co.nz> wrote:
Hmm... This should optionally apply to time. e.g.
time_i_got_up_in_the_morning should reflect the time zone where I got up
- if I got up at 8am NZ time then this should be displayed, not 12pm (12
noon) to someone in Los Angeles or 3am in Tokyo! (have a 'localtime'
data type?- possibly add the timezone code if displayed in a different
time zone.)
it was 12 noon in LA when you got up.
if you want the local time of the even you can specfy where you want it
at time zone 'Pacific/Auckland'
at time zone 'NZDT' -- note: some names are ambiguous eg: 'EST'
or
at time zone '-13:00' -- note: offsets are ISO, not POSIX
getting the local time of the even This requires that you store the locale, zone name , or offset when
you store the time.
or you could just cast it to text when you store it...
how confusing is 'EST' ?
worse than this:
set datestyle to 'sql,dmy';
set time zone 'Australia/Brisbane';
select '20130101T000000Z'::timestamptz;
set time zone 'Australia/Sydney';
select '20130101T000000Z'::timestamptz;
set time zone 'America/New_York';
select '20130101T000000Z'::timestamptz;
--
⚂⚃ 100% natural
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Fri, Mar 29, 2013 at 10:39 AM, Jasen Betts <jasen@xnet.co.nz> wrote:
how confusing is 'EST' ?
worse than this:set datestyle to 'sql,dmy';
set time zone 'Australia/Brisbane';
select '20130101T000000Z'::timestamptz;
set time zone 'Australia/Sydney';
select '20130101T000000Z'::timestamptz;
set time zone 'America/New_York';
select '20130101T000000Z'::timestamptz;
As a Melburnian (that is, I live in Australia/Melbourne, which is in
the same timezone as Sydney - but not Brisbane), I prefer to call it
EAST - Eastern Australian Standard Time.
Of course, for anything that really matters, I'll work with UTC. Much safer.
ChrisA
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Some people wrote:
... Hmm... This should optionally apply to time.
... for anything that really matters, I'll work with UTC.
Is there a Godwin's law
<http://en.wikipedia.org/wiki/Godwin's_law> equivalent for when
our conversations end up with timezones getting mentioned? :)
Regards
Gavan Schneider
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Some thoughts.
The current MONEY type might be considered akin to ASCII.
Perfect for a base US centric accounting system where there are
cents and dollars and no need to carry smaller fractions. As
discussed, there are some details that could be refined.
When it comes to this type being used in full blown money
systems it lacks the ability to carry fractions of cents and
keep track of currencies. It also needs to play nicer with other
exact types such as numeric, i.e., no intermediate calculations
as real.
Therefore the discussion is really about the desired role for
the MONEY type. Should it be refined in its current dallar and
cents mode? or, be promoted to a more universal role (akin to a
shift from ASCII to UTF)?
If there is merit in making MONEY work for most situations
involving financial transactions I think the following might apply:
- keep integer as the underlying base type (for performance)
- generalise the decimal multiplier of a MONRY column so a
specific MONEY column can be what its creator wants (from
partial cents to millions of dollars/Yen/Other, along with
rounding/truncating rules as required by r the user of his/her
external agencies)
- define the currency for a given column and only allow this to
change in defined ways, and specifically forbid implicit changes
such as would arise from altering LOCALE information
- ensure the MONEY type plays nice with other exact precision
types, i.e., convert to REAL/FLOAT as a very last resort
Personally I don't think it is appropriate for the MONEY type to
have variable characteristics (such as different currencies)
within a given column, rather the column variable should define
the currency along with the desired decimal-multiplier and
whatever else is required. The actual values within the column
remain as simple integers. This is mostly based on performance
issues. If the MONRY type is to be used it has to offer real
performance benefits over bespoke NUMERIC applications.
Regards
Gavan Schneider
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Gavan Schneider <pg-gts@snkmail.com> writes:
Therefore the discussion is really about the desired role for
the MONEY type. Should it be refined in its current dallar and
cents mode? or, be promoted to a more universal role (akin to a
shift from ASCII to UTF)?
Well, this has been discussed before, and the majority view every
time has been that MONEY is a legacy thing that most people would
rather rip out than sink a large amount of additional effort into.
It has some use-cases but they are narrow, and it's not clear how
much wider the use-cases would be if we tried to generalize it.
My own experience with this sort of thing leads me to think that
real applications dealing with a variety of currencies will be
needing to store additional details, such as the exact exchange
rate that applied to a particular transaction. So while merely
decoupling MONEY from lc_monetary doesn't sound like a bad thing,
it's not clear it really buys that much.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Fri, 29 Mar 2013 11:46:40 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:
Well, this has been discussed before, and the majority view every
time has been that MONEY is a legacy thing that most people would
rather rip out than sink a large amount of additional effort into.
It has some use-cases but they are narrow, and it's not clear how
much wider the use-cases would be if we tried to generalize it.
I wonder if our vision isn't a little tunneled here. Using this type
for money is, perhaps, a specialized use and the type should really be
called something else and modified to remove all connotations of money
from it. So...
- Drop the currency symbol
- Allow number of decimals to be defined once for the column
- Don't use locale except to specify decimal separator (',' vs. '.')
- Allow operations against numeric
Not sure what to rename it to. Decimal would be good if it wasn't
already in use. Maybe DecimalInt.
My own experience with this sort of thing leads me to think that
real applications dealing with a variety of currencies will be
needing to store additional details, such as the exact exchange
rate that applied to a particular transaction. So while merely
Seems like something that can be stored in a different column.
--
D'Arcy J.M. Cain <darcy@druid.net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 788 2246 (DoD#0082) (eNTP) | what's for dinner.
IM: darcy@Vex.Net, VOIP: sip:darcy@Vex.Net--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 28 March 2013 13:52, Shaun Thomas <sthomas@optionshouse.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.
On Thu, 2013-03-28 at 23:43 +1100, Gavan Schneider wrote:
If the money type is meant to be serious then these
conventions need to be followed/settable on a column by column
basis.
I don't like the idea of tying the semantics to a column. That leaves
out values that aren't stored in a column, e.g. literals or the results
of some expression.
Regards,
Jeff Davis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
