5

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)
1
  • Clear, complete, and to-the-point, +1. In future, please include your PostgreSQL version and preferably the OS/version you're on, just because sometimes it's important. Sep 1, 2014 at 8:37

1 Answer 1

3

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.

1
  • I've solved it using the round function.
    – kev
    Sep 1, 2014 at 8:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.