Re: [GENERAL] strange sum behaviour

[Ron Johnson]

-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  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-

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] strange sum behaviour

[Alvaro Herrera]

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  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 Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] strange sum behaviour

[Joshua D. Drake]

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  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/



---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] strange sum behaviour

[Ron Johnson]

-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  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-

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] strange sum behaviour

[Chris Mair]

> 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


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] strange sum behaviour

[John D. Burger]

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


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] strange sum behaviour

[Andrew Baerg]

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 <[EMAIL PROTECTED]> 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



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] strange sum behaviour

[Seneca Cunningham]

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


--
Seneca Cunningham
[EMAIL PROTECTED]




---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] strange sum behaviour

[Peter Eisentraut]

> 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/

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] strange sum behaviour

[codeWarrior]

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" <[EMAIL PROTECTED]> wrote in message 
news:[EMAIL PROTECTED]
>
> "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.
> 



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] strange sum behaviour

[Karen Hill]

"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


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] strange sum behaviour

[Andrew Baerg]

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

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster