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>:
Show quoted text
"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>:
Show quoted text
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