strange sum behaviour

"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

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&gt; for
some information about why you should be using numeric for your
amount column.

--
Seneca Cunningham
tentra@gmail.com

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:

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

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

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&gt; 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&gt; 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&gt; 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&gt; 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-----