Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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. –  Craig Ringer Sep 1 at 8:37

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
I've solved it using the round function. –  user1252307 Sep 1 at 8:36

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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