Re: 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(at)stz-bg(dot)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(dot)consulting(at)comcast(dot)net
p: 305.321.1144

On Fri, 05 Aug 2011 07:20:01 -0400, Jerry Sievers wrote:
> Condor <condor(at)stz-bg(dot)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(dot)consulting(at)comcast(dot)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(at)stz-bg(dot)com]
> Sent: Friday, August 05, 2011 6:49 AM
> To: pgsql-general(at)postgresql(dot)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(at)perceptron(dot)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