From: | "Frank Millman" <frank(at)chagford(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Unexpected result using floor() function |
Date: | 2016-03-15 05:57:25 |
Message-ID: | F4B282C662834BC9AC107A210DC4ED47@FrankLaptop |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> I am running PostgreSQL 9.4.4 on Fedora 22.
>
> SELECT floor(4.725 * 100 + 0.5) returns 473, which is what I expected.
>
> SELECT floor(4.725 * power(10, 2) + 0.5) returns 472, which I find surprising.
>
> Please can someone explain the anomaly.
I think I have a solution to my problem, but I would appreciate a review in case I have missed some corner cases.
I understand it better now. Here are some of the things I have learned.
1. In Python, 4.725 is assumed to be a float. You need some extra steps to turn it into a Decimal type. PostgreSQL seems to take the opposite approach – it is assumed to be numeric, unless you explicitly cast it to a float.
2. As pointed out, there are two forms of the power function.
test=> select pg_typeof(power(10, 2));
pg_typeof
------------------
double precision
test=> select pg_typeof(power(10., 2));
pg_typeof
----------
numeric
I found that adding a decimal point after the 10 is the easiest way to force it to return a numeric.
Putting this together, my solution is -
test=> select floor(4.725 * power(10., 2) + 0.5);
floor
-------
473
Can anyone see any problems with this?
Thanks
Frank
From | Date | Subject | |
---|---|---|---|
Next Message | Francisco Olarte | 2016-03-15 10:02:36 | Re: Unexpected result using floor() function |
Previous Message | David G. Johnston | 2016-03-15 02:57:49 | Re: Unique UUID value - PostgreSQL 9.2 |