Try this:
create table test (f float);
insert into test values (330.0);
commit;
select (8 + 330.0/60)::int; --14
select (8 + f/60)::int from test; --14
select (9 + 330.0/60)::int; --15
select (9 + f/60)::int from test; --14
Can someone explain why the last query returns 14 instead of 15?
PostgreSQL 9.3.9 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
12.04.5 LTS (GNU/Linux 3.2.0-63-virtual x86_64)
It's an inconsistency between rounding of numeric and float types.
regress=> select pg_typeof (9 + 330.0/60);
pg_typeof
-----------
numeric
(1 row)
regress=> select pg_typeof(9 + f/60) from test;
pg_typeof
------------------
double precision
(1 row)
regress=> select (9 + 330.0/60);
?column?
---------------------
14.5000000000000000
(1 row)
regress=> select (9 + f/60) from test;
?column?
----------
14.5
(1 row)
regress=> select (NUMERIC '14.5000000000000000')::integer;
int4
------
15
(1 row)
regress=> select (FLOAT8 '14.5000000000000000')::integer;
int4
------
14
(1 row)
In general, relying on exact rounding of floating point can produce surprising results at exact border values, because many decimal values aren't represented exactly in floating point.
If you want strict rounding, consider working with numeric types consistently. You may also find the explicit round function useful.
Unfortunately, as far as I know PostgreSQL doesn't offer selectable rounding rules or a round variant that lets you choose the rounding mode (bankers rounding, always down, even, odd, etc) like that provided by Java.
