Thread: strange sum behaviour
Hi,
I am getting strange results from the sum function as follows:
corp=# select amount from acc_trans where trans_id=19721 and chart_id=10019;
amount
---------
4.88
117.1
-121.98
(3 rows)
corp=# select sum(amount) from acc_trans where trans_id=19721 and
chart_id=10019;
sum
----------------------
-1.4210854715202e-14
(1 row)
amount is defined as double precision. I noticed that if I cast amount
as numeric, the sum comes out 0 as expected.
I am using postgresql 8.0.1
Thanks in advance for any help or suggestions.
Andrew Baerg
"Andrew Baerg" wrote: > Hi, > > I am getting strange results from the sum function as follows: > > corp=# select amount from acc_trans where trans_id=19721 and chart_id=10019; > amount > --------- > 4.88 > 117.1 > -121.98 > (3 rows) > > corp=# select sum(amount) from acc_trans where trans_id=19721 and > chart_id=10019; > sum > ---------------------- > -1.4210854715202e-14 > (1 row) > > > amount is defined as double precision. I noticed that if I cast amount > as numeric, the sum comes out 0 as expected. > You are using the wrong datatype if you are working with currency. Use Numeric or Decimal instead. The "money" type is depreciated. http://www.postgresql.org/docs/8.1/interactive/datatype-money.html
As an alternative -- you could do an inline type cast.... SELECT SUM(amount)::numeric(10, 2) FROM acc_trans WHERE trans_id=19721 AND chart_id=10019; "Karen Hill" <karen_hill22@yahoo.com> wrote in message news:1156873638.219228.218710@74g2000cwt.googlegroups.com... > > "Andrew Baerg" wrote: >> Hi, >> >> I am getting strange results from the sum function as follows: >> >> corp=# select amount from acc_trans where trans_id=19721 and >> chart_id=10019; >> amount >> --------- >> 4.88 >> 117.1 >> -121.98 >> (3 rows) >> >> corp=# select sum(amount) from acc_trans where trans_id=19721 and >> chart_id=10019; >> sum >> ---------------------- >> -1.4210854715202e-14 >> (1 row) >> >> >> amount is defined as double precision. I noticed that if I cast amount >> as numeric, the sum comes out 0 as expected. >> > > Double precision accorrding to the documentation is "8 byte > variable-precision, inexact". That means when you do the sum, rounding > occurs. You should use Numeric or Decimal as the datatype. The money > type is depreciated so don't use it if what you are summing is currency. >
> corp=# select sum(amount) from acc_trans where trans_id=19721 and > chart_id=10019; > sum > ---------------------- > -1.4210854715202e-14 [expected to be 0] Floating-point numbers are typically inaccurate like that, and if you rely in equality comparisons, you're doing something wrong. You should use numeric or fix your application to take these errors into account. -- Peter Eisentraut http://developer.postgresql.org/~petere/
On 29-Aug-2006, at 13:13 :48, Andrew Baerg wrote: > Hi, > > I am getting strange results from the sum function as follows: > > corp=# select amount from acc_trans where trans_id=19721 and > chart_id=10019; > amount > --------- > 4.88 > 117.1 > -121.98 > (3 rows) > > corp=# select sum(amount) from acc_trans where trans_id=19721 and > chart_id=10019; > sum > ---------------------- > -1.4210854715202e-14 > (1 row) > > > amount is defined as double precision. I noticed that if I cast amount > as numeric, the sum comes out 0 as expected. > Don't use floats or doubles for financial data, use numeric types. Your double sum looks to be a normal error for floats in that situation. Take a look at <http://www2.hursley.ibm.com/decimal/decifaq.html> for some information about why you should be using numeric for your amount column. -- Seneca Cunningham tentra@gmail.com
Thanks for so many prompt responses. I have researched the differences between floating point and arbitrary precision numbers in the pgsql docs and understand now what is happening. Thanks again to the many great members of the pgsql community. Andrew On 8/29/06, Andrew Baerg <andrew.baerg@gmail.com> wrote: > Hi, > > I am getting strange results from the sum function as follows: > > corp=# select amount from acc_trans where trans_id=19721 and chart_id=10019; > amount > --------- > 4.88 > 117.1 > -121.98 > (3 rows) > > corp=# select sum(amount) from acc_trans where trans_id=19721 and > chart_id=10019; > sum > ---------------------- > -1.4210854715202e-14 > (1 row) > > > amount is defined as double precision. I noticed that if I cast amount > as numeric, the sum comes out 0 as expected. > > I am using postgresql 8.0.1 > > Thanks in advance for any help or suggestions. > > Andrew Baerg >
Andrew Baerg wrote:
> corp=# select amount from acc_trans where trans_id=19721 and
> chart_id=10019;
> amount
> ---------
> 4.88
> 117.1
> -121.98
> (3 rows)
>
> corp=# select sum(amount) from acc_trans where trans_id=19721 and
> chart_id=10019;
> sum
> ----------------------
> -1.4210854715202e-14
> (1 row)
>
This has nothing to do with SUM():
> select 4.88::float + 117.1::float + -121.98::float;
?column?
----------------------
-1.4210854715202e-14
(1 row)
It's just the inherent inexactness of floating point, and probably not
even particular to Postgres.
- John D. Burger
MITRE
> corp=# select amount from acc_trans where trans_id=19721 and chart_id=10019; > amount > --------- > 4.88 > 117.1 > -121.98 > (3 rows) > > corp=# select sum(amount) from acc_trans where trans_id=19721 and > chart_id=10019; > sum > ---------------------- > -1.4210854715202e-14 > (1 row) > > > amount is defined as double precision. I noticed that if I cast amount > as numeric, the sum comes out 0 as expected. 0.1 cannot be represented exactly using floating point numbers (the same way as 1/3 cannot be represented exactly using decimal numbers). You're bound to suffer from round-off errors. Use numeric for exact, decimal math. Bye, Chris. -- Chris Mair http://www.1006.org
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Seneca Cunningham wrote: > On 29-Aug-2006, at 13:13 :48, Andrew Baerg wrote: [snip] > Take a look at <http://www2.hursley.ibm.com/decimal/decifaq.html> for > some information about why you should be using numeric for your amount > column. So how does PG implement Decimal? - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFE9MRDS9HxQb37XmcRAgfEAJwKv/6K6CgaCQukQsE79OcYM7MMVwCdFjPS dOR1oyNM954cAWHW493SUv0= =Qy0I -----END PGP SIGNATURE-----
Ron Johnson wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Seneca Cunningham wrote: >> On 29-Aug-2006, at 13:13 :48, Andrew Baerg wrote: > [snip] >> Take a look at <http://www2.hursley.ibm.com/decimal/decifaq.html> for >> some information about why you should be using numeric for your amount >> column. > > So how does PG implement Decimal? As mentioned above, please look at numeric. :) Sincerely, Joshua D. Drake > > - -- > Ron Johnson, Jr. > Jefferson LA USA > > Is "common sense" really valid? > For example, it is "common sense" to white-power racists that > whites are superior to blacks, and that those with brown skins > are mud people. > However, that "common sense" is obviously wrong. > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.5 (GNU/Linux) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org > > iD8DBQFE9MRDS9HxQb37XmcRAgfEAJwKv/6K6CgaCQukQsE79OcYM7MMVwCdFjPS > dOR1oyNM954cAWHW493SUv0= > =Qy0I > -----END PGP SIGNATURE----- > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/
Ron Johnson wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Seneca Cunningham wrote: > > On 29-Aug-2006, at 13:13 :48, Andrew Baerg wrote: > [snip] > > Take a look at <http://www2.hursley.ibm.com/decimal/decifaq.html> for > > some information about why you should be using numeric for your amount > > column. > > So how does PG implement Decimal? http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/numeric.c?rev=1.94;content-type=text%2Fx-cvsweb-markup -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Alvaro Herrera wrote: > Ron Johnson wrote: >> -----BEGIN PGP SIGNED MESSAGE----- >> Hash: SHA1 >> >> Seneca Cunningham wrote: >>> On 29-Aug-2006, at 13:13 :48, Andrew Baerg wrote: >> [snip] >>> Take a look at <http://www2.hursley.ibm.com/decimal/decifaq.html> for >>> some information about why you should be using numeric for your amount >>> column. >> So how does PG implement Decimal? > > http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/numeric.c?rev=1.94;content-type=text%2Fx-cvsweb-markup Thanks. - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFE9N2rS9HxQb37XmcRAplmAKCHYRrv4e4Y4RAweQiJqlEe9PPAMgCggdv2 OX0hHo8jC7l6rR2i/0+vy/I= =KFxv -----END PGP SIGNATURE-----