Thread: Postgresql problem with update double precision
Hello ppl, for few years I have problem when update double precision field. I have table and few double precision columns, here is example: sumall double precision, sumin double precision, My php script do: $get = 2.40 and sql code is: UPDATE table1 SET sumall = sumall + $get WHERE id = 1 AND rd = CURRENT_DATE; When I browse the table some times i see incorrect values like: 955.599999999998 it's should be 955.60 after these updates ... some days is fine, some days the value is incorrect. I have this problem from version 7 of postgresql, Im now use 9.0.3 Anyone know what can be the problem and why some times records is fine, some times isnt ? -- Regards, Condor
Condor <condor@stz-bg.com> writes: > Hello ppl, > for few years I have problem when update double precision field. I > have table and few double precision columns, here is example: > > sumall double precision, > sumin double precision, > > My php script do: > > $get = 2.40 > > and sql code is: > > UPDATE table1 SET sumall = sumall + $get WHERE id = 1 AND rd = > CURRENT_DATE; > > > When I browse the table some times i see incorrect values like: > > 955.599999999998 > > it's should be 955.60 after these updates ... some days is fine, some > days the value is incorrect. > > I have this problem from version 7 of postgresql, Im now use 9.0.3 > > Anyone know what can be the problem and why some times records is > fine, some times isnt ? That floating point data types are inexact is a well known problem with them and not Postgres specific. Consider switching those fields to type NUMERIC. HTH > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net p: 305.321.1144
On Fri, 05 Aug 2011 07:20:01 -0400, Jerry Sievers wrote: > Condor <condor@stz-bg.com> writes: > >> Hello ppl, >> for few years I have problem when update double precision field. I >> have table and few double precision columns, here is example: >> >> sumall double precision, >> sumin double precision, >> >> My php script do: >> >> $get = 2.40 >> >> and sql code is: >> >> UPDATE table1 SET sumall = sumall + $get WHERE id = 1 AND rd = >> CURRENT_DATE; >> >> >> When I browse the table some times i see incorrect values like: >> >> 955.599999999998 >> >> it's should be 955.60 after these updates ... some days is fine, >> some >> days the value is incorrect. >> >> I have this problem from version 7 of postgresql, Im now use 9.0.3 >> >> Anyone know what can be the problem and why some times records is >> fine, some times isnt ? > > That floating point data types are inexact is a well known problem > with them and not Postgres specific. > > Consider switching those fields to type NUMERIC. > > HTH >> > > -- > Jerry Sievers > Postgres DBA/Development Consulting > e: postgres.consulting@comcast.net > p: 305.321.1144 Thank you, today I see all 3 rows is normal, but when I do select sum(sumall) I got 73.3000000001 as result. Any way how I can convert field in numeric without to lose data ? -- Regards, Condor
> -----Original Message----- > From: Condor [mailto:condor@stz-bg.com] > Sent: Friday, August 05, 2011 6:49 AM > To: pgsql-general@postgresql.org > Subject: Postgresql problem with update double precision > > > Hello ppl, > for few years I have problem when update double precision field. I have > table and few double precision columns, here is example: > > sumall double precision, > sumin double precision, > > My php script do: > > $get = 2.40 > > and sql code is: > > UPDATE table1 SET sumall = sumall + $get WHERE id = 1 AND rd = > CURRENT_DATE; > > > When I browse the table some times i see incorrect values like: > > 955.599999999998 > > it's should be 955.60 after these updates ... some days is fine, some > days the value is incorrect. > > I have this problem from version 7 of postgresql, Im now use 9.0.3 > > Anyone know what can be the problem and why some times records is fine, > some times isnt ? > > -- > Regards, > Condor "double precision" is imprecise data type, that's why you see what you see. If you want to avoid your problem, switch to NUMERIC(precision, scale), which is precise data type. Alter the type of your "double" columns. Regards, Igor Neyman
On Fri, Aug 5, 2011 at 7:32 AM, Igor Neyman <ineyman@perceptron.com> wrote: > If you want to avoid your problem, switch to NUMERIC(precision, scale), which is precise data type. > Alter the type of your "double" columns. > I'd suggest NUMERIC without specifying precision or scale. That gives you the most flexibility. I would only specify precision and scale if these are to be enforced on data input. Best Wishes, Chris Travers