dividing money by money
in [PgSql]
|
From: Tom Lane on 16 Jul 2010 10:36 Andy Balholm <andy(a)balholm.com> writes: > On Jul 15, 2010, at 7:25 PM, Tom Lane wrote: >> * I didn't like this bit in cash_numeric(): >> >> result->n_sign_dscale = NUMERIC_SIGN(result) | fpoint; >> >> Not only is that unwarranted chumminess with the implementation of >> numeric, it's flat-out wrong. If the result isn't exactly the right >> number of digits (say, it's 12.33999999 instead of the desired 12.34) >> this just hides the extra digits, it doesn't make the result correct. >> The right way is to use numeric_round(), which not only sets the dscale >> where we want it but rounds off any inaccuracy that might have crept in >> from the division. > Sorry about that. Is there documentation anywhere for backend > functions and types? Nothing at that level of detail, unfortunately, beyond the code itself. If you'd read the comments near the head of numeric.c, maybe the mistake would've been apparent to you, or maybe not. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Tom Lane on 16 Jul 2010 10:31 "Kevin Grittner" <Kevin.Grittner(a)wicourts.gov> writes: > Peter Eisentraut <peter_e(a)gmx.net> wrote: >> I didn't see any discussion about why this should return float8 >> rather than numeric. It seems wrong to use float8 for this. > That discussion took place several months ago on the -bugs list. > I'll paste some links from a quick search of the archives below. > Since multiplication of money is by float8 and not numeric, it > ultimately seemed more consistent to me to have the results of > division be float8. I felt that as long as we had a cast between > money and numeric, someone could always cast to numeric if they > wanted that style of division. Yeah. The other argument that I found convincing was that if the operator was defined to yield numeric, people might think that the result was exact ... which of course it won't be, either way. Choosing float8 helps to remind the user it's an approximate quotient. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Andy Balholm on 16 Jul 2010 10:13 On Jul 15, 2010, at 7:25 PM, Tom Lane wrote: > * I didn't like this bit in cash_numeric(): > > result->n_sign_dscale = NUMERIC_SIGN(result) | fpoint; > > Not only is that unwarranted chumminess with the implementation of > numeric, it's flat-out wrong. If the result isn't exactly the right > number of digits (say, it's 12.33999999 instead of the desired 12.34) > this just hides the extra digits, it doesn't make the result correct. > The right way is to use numeric_round(), which not only sets the dscale > where we want it but rounds off any inaccuracy that might have crept in > from the division. Sorry about that. Is there documentation anywhere for backend functions and types? I couldn't find any, so I just looked through numeric.h to see what looked like it might work. I didn't find numeric_round, since it's declared in builtins.h. -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: "Kevin Grittner" on 16 Jul 2010 09:55 Peter Eisentraut <peter_e(a)gmx.net> wrote: > I didn't see any discussion about why this should return float8 > rather than numeric. It seems wrong to use float8 for this. That discussion took place several months ago on the -bugs list. I'll paste some links from a quick search of the archives below. Since multiplication of money is by float8 and not numeric, it ultimately seemed more consistent to me to have the results of division be float8. I felt that as long as we had a cast between money and numeric, someone could always cast to numeric if they wanted that style of division. http://archives.postgresql.org/pgsql-bugs/2010-03/msg00233.php http://archives.postgresql.org/pgsql-bugs/2010-03/msg00241.php http://archives.postgresql.org/pgsql-bugs/2010-03/msg00244.php http://archives.postgresql.org/pgsql-bugs/2010-03/msg00245.php http://archives.postgresql.org/pgsql-bugs/2010-04/msg00006.php -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Peter Eisentraut on 17 Jul 2010 06:18
On fre, 2010-07-16 at 12:21 -0400, Tom Lane wrote: > Actually ... the thing that might turn money into a less deprecated > type > is if you could set lc_monetary per column. I wonder whether Peter's > collation hack could be extended to deal with that. In principle yes. -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |