Postgres Pro
Downloads
Home   >   mailing lists

Thread: Rounding Problems?

Rounding Problems?

From
elbriga
Date:
Hi,
  I have this pl function:
CREATE OR REPLACE FUNCTION ceilDecimal(num float) RETURNS float AS
$BODY$
BEGIN
    RETURN CEIL(num * 100) / 100;
END
$BODY$
LANGUAGE 'plpgsql';

  It is supposed to do a "decimail ceil" for 2 decimal places.
  But when I do "SELECT ceilDecimal(0.07)"
It will return
 ceildecimal
-------------
        0.08

  For other numbers the result is as expected:
SELECT ceilDecimal(0.17);
 ceildecimal
-------------
        0.17

WHY? rsrsr

Thanks in advance!



--
View this message in context: http://postgresql.nabble.com/Rounding-Problems-tp5932388.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: Rounding Problems?

From
Albe Laurenz
Date:
elbriga wrote:
> Hi,
>   I have this pl function:
> CREATE OR REPLACE FUNCTION ceilDecimal(num float) RETURNS float AS
> $BODY$
> BEGIN
>     RETURN CEIL(num * 100) / 100;
> END
> $BODY$
> LANGUAGE 'plpgsql';
> 
>   It is supposed to do a "decimail ceil" for 2 decimal places.
>   But when I do "SELECT ceilDecimal(0.07)"
> It will return
>  ceildecimal
> -------------
>         0.08
> 
>   For other numbers the result is as expected:
> SELECT ceilDecimal(0.17);
>  ceildecimal
> -------------
>         0.17
> 
> WHY? rsrsr

Let's do your calculation step by step.

100 is (implicitly) an integer value.

When a "real" and an "integer" are multiplied, PostgreSQL casts them to
"double precision" before the operation. That would be necessary anyway,
because "ceil()" only operates on "double precision" (or "numeric").

test=> SELECT CAST (REAL '0.07' AS double precision);
       float8
--------------------
 0.0700000002980232
(1 row)

The weird digits are because 0.07 can never represented exactly
as a floating point number (with base 2).
They become visible because "double precision" has greater precision.

test=> SELECT REAL '0.07' * 100;
     ?column?
------------------
 7.00000002980232
(1 row)

test=> SELECT ceil(REAL '0.07' * 100);
 ceil
------
    8
(1 row)

The value is rounded up correctly, because it is greater than 7.


For some "real" values, the representation will be slightly less
then the correct value:

test=> SELECT CAST (REAL '0.47' AS double precision);
      float8
-------------------
 0.469999998807907
(1 row)

For such values, your function will work as you expect.

You could work around the problem by subtracting a small delta
from the value after converting it to "double precision".

Yours,
Laurenz Albe

Re: Rounding Problems?

From
elbriga
Date:
Thanks for the detailed answer!

Changing the function sinature seams to have solved the problem:
CREATE OR REPLACE FUNCTION ceilDecimal(num numeric) RETURNS float AS
$BODY$
BEGIN
    RETURN CEIL(num * 100) / 100;
END
$BODY$
LANGUAGE 'plpgsql';


SELECT ceilDecimal(0.07);
 ceildecimal
-------------
        0.07




--
View this message in context: http://postgresql.nabble.com/Rounding-Problems-tp5932388p5932397.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: Rounding Problems?

From
Albe Laurenz
Date:
elbriga wrote:
> Thanks for the detailed answer!
> 
> Changing the function sinature seams to have solved the problem:
> CREATE OR REPLACE FUNCTION ceilDecimal(num numeric) RETURNS float AS
> $BODY$
> BEGIN
>     RETURN CEIL(num * 100) / 100;
> END
> $BODY$
> LANGUAGE 'plpgsql';
> 
> 
> SELECT ceilDecimal(0.07);
>  ceildecimal
> -------------
>         0.07

Yes, because "numeric" stores the exact number, so it will be
exactly 0.07 after conversion.

Yours,
Laurenz Albe