Re: Unexpected result using floor() function

Hi Frank:

On Tue, Mar 15, 2016 at 6:57 AM, Frank Millman <frank@chagford.com> wrote:
> 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?

I see a problem in it relying in interpretation of constants. From my
experience I would recommend explicit casts, it's just a second longer
to type but much clearer. The problems start with 10 being interpreted
as integer, all the other ones as numeric:

s=> select pg_typeof(10.), pg_typeof(10), pg_typeof(10.E0), pg_typeof(10E0);
 pg_typeof | pg_typeof | pg_typeof | pg_typeof
-----------+-----------+-----------+-----------
 numeric   | integer   | numeric   | numeric
(1 row)

This may byte you any day, so I wuld recommend doing

s=> select v, pg_typeof(v) from (select floor(4.725 * power(cast(10.
as numeric), 2) + 0.5)) as aux(v);
  v  | pg_typeof
-----+-----------
 473 | numeric
(1 row)
s=> select v, pg_typeof(v) from (select floor(4.725 * power(cast(10 as
numeric), 2) + 0.5)) as aux(v);
  v  | pg_typeof
-----+-----------
 473 | numeric
(1 row)

which makes your intention clear.

Francisco Olarte.