[GENERAL] 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.5998 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql problem with update double precision
Condor con...@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.5998 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.consult...@comcast.net p: 305.321.1144 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql problem with update double precision
On Fri, 05 Aug 2011 07:20:01 -0400, Jerry Sievers wrote: Condor con...@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.5998 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.consult...@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.31 as result. Any way how I can convert field in numeric without to lose data ? -- Regards, Condor -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql problem with update double precision
-Original Message- From: Condor [mailto:con...@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.5998 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Postgresql problem with update double precision
On Fri, Aug 5, 2011 at 7:32 AM, Igor Neyman iney...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
