From: | Francisco Olarte <folarte(at)peoplecall(dot)com> |
---|---|
To: | Frank Millman <frank(at)chagford(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Unexpected result using floor() function |
Date: | 2016-03-15 10:02:36 |
Message-ID: | CA+bJJbxMhM6Av7bpTiJghCuaM+644NFGfZM9woZBYcyzx047hA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi Frank:
On Tue, Mar 15, 2016 at 6:57 AM, Frank Millman <frank(at)chagford(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Frank Millman | 2016-03-15 10:48:44 | Re: Unexpected result using floor() function |
Previous Message | Frank Millman | 2016-03-15 05:57:25 | Re: Unexpected result using floor() function |