Re: Unexpected result using floor() function

>

> 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