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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Try this:

create table test (f float);
insert into test values (330.0);

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)
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 '14 at 8:37
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);
(1 row)

regress=> select pg_typeof(9 + f/60) from test;
 double precision
(1 row)

regress=> select (9 + 330.0/60);
(1 row)

regress=> select (9 + f/60) from test;
(1 row)

regress=> select (NUMERIC '14.5000000000000000')::integer;
(1 row)

regress=> select (FLOAT8 '14.5000000000000000')::integer;
(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. – kev Sep 1 '14 at 8:36

Your Answer


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.