Обсуждение: BUG #5484: sum() bug
The following bug has been logged online:
Bug reference: 5484
Logged by: Sergey
Email address: viras@yandex.ru
PostgreSQL version: 8.4.4
Operating system: FreeBSD 7.3
Description: sum() bug
Details:
Function sum() bug.
8849.15+6464.57=15313.72
But sum()=15313.7
[root@proxy ~]# psql -U pgsql megafon
psql (8.4.4)
Type "help" for help.
megafon=# SELECT version();
version
----------------------------------------------------------------------------
-----------------------------
PostgreSQL 8.4.4 on i386-portbld-freebsd7.3, compiled by GCC cc (GCC) 4.2.1
20070719 [FreeBSD], 32-bit
(1 row)
megafon=#
megafon=# SELECT * from aaa;
num
---------
8849.15
6464.57
(2 rows)
megafon=# SELECT sum(num) from aaa;
sum
---------
15313.7
(1 row)
megafon=#
"Sergey" <viras@yandex.ru> writes:
> 8849.15+6464.57=15313.72
> But sum()=15313.7
You did not show what datatype you're summing, but if the column is
float4 this result wouldn't be surprising. float4 is only good
to about six decimal digits.
regards, tom lane
megafon=# \d aaa
Table "public.aaa"
Column | Type | Modifiers
--------+------+-----------
num | real |
Yes, really, on smaller quantity of digits of errors is not present.
What type of the data is better for using? Numbers up to 100000 and accuracy of 2 fractional signs.
30.05.10, 21:43, "Tom Lane" <tgl@sss.pgh.pa.us>:
> "Sergey" <viras@yandex.ru> writes:8849.15+6464.57=15313.72But sum()=15313.7You did not show what datatype you're
summing,but if the column isfloat4 this result wouldn't be surprising. float4 is only goodto about six decimal digits.
regards, tom lane
On Tue, Jun 1, 2010 at 9:24 AM, viras <viras@yandex.ru> wrote: > megafon=# \d aaa > Table "public.aaa" > Column | Type | Modifiers > --------+------+----------- > num | real | > > Yes, really, on smaller quantity of digits of errors is not present. > What type of the data is better for using? Numbers up to 100000 and accuracy of 2 fractional signs. numeric is a good choice to avoid loss of precision, but can be a bit slower. You could also try float8. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company
Robert Haas <robertmhaas@gmail.com> wrote: > On Tue, Jun 1, 2010 at 9:24 AM, viras <viras@yandex.ru> wrote: >> What type of the data is better for using? Numbers up to 100000 >> and accuracy of 2 fractional signs. > > numeric is a good choice to avoid loss of precision, but can be a > bit slower. > > You could also try float8. Yeah, as long as you remember that this is an *approximate* data type. If you really mean that you're satisfied with an *accuracy* of two fractional digits for a number up to 100000, you are OK. But realize that means that 1.01 would actually be 1.0100000000000000088817841970012523233890533447265625 and that 100000.01 would actually be 100000.009999999994761310517787933349609375 -- accurate to far more than two decimal digits, but not *exact*. If you want exact values based on decimal fractions, you should use numeric. -Kevin
Many thanks for the help! numeric is my choice :) 01.06.10, 19:19, "Kevin Grittner" <Kevin.Grittner@wicourts.gov>: > Robert Haas wrote: > > On Tue, Jun 1, 2010 at 9:24 AM, viras wrote: > > >> What type of the data is better for using? Numbers up to 100000 > >> and accuracy of 2 fractional signs. > > > > numeric is a good choice to avoid loss of precision, but can be a > > bit slower. > > > > You could also try float8. > > Yeah, as long as you remember that this is an *approximate* data > type. If you really mean that you're satisfied with an *accuracy* > of two fractional digits for a number up to 100000, you are OK. But > realize that means that 1.01 would actually be > 1.0100000000000000088817841970012523233890533447265625 and that > 100000.01 would actually be > 100000.009999999994761310517787933349609375 -- accurate to far more > than two decimal digits, but not *exact*. > > If you want exact values based on decimal fractions, you should use > numeric. > > -Kevin > >