From: | Albe Laurenz <laurenz(dot)albe(at)wien(dot)gv(dot)at> |
---|---|
To: | "'elbriga *EXTERN*'" <gabrielortizlour(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Rounding Problems? |
Date: | 2016-11-29 13:09:25 |
Message-ID: | A737B7A37273E048B164557ADEF4A58B5399E56C@ntex2010i.host.magwien.gv.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | elbriga | 2016-11-29 13:43:24 | Re: Rounding Problems? |
Previous Message | Jacob Bunk Nielsen | 2016-11-29 12:34:35 | Re: Storing files: 2.3TBytes, 17M file count |