> select ROUND(0.5) ; returns 0
> select ROUND(1.5) ; returns 2;
> select ROUND(2.5) ; returns 2;
> select ROUND(3.5) ; returns 4;....so on .
> I'm sure you would have figured out what's happening !!! Why ??
> How do I get to approximate any number x.5 as x+1 ??
Looks like a bug to me:
test=# select * from pg_proc where proname = 'round';proname | proowner | prolang | proisinh | proistrusted |
proiscachable|
proisstrict | pronargs | proretset | prorettype | proargtypes | probyte_pct
| properbyte_cpu | propercall_cpu | prooutin_ratio | prosrc |
probin
---------+----------+---------+----------+--------------+---------------+---
----------+----------+-----------+------------+-------------+-------------+-
---------------+----------------+----------------+--------------------+-----
---round | 1 | 12 | f | t | t | t
| 1 | f | 701 | 701 | 100 |
0 | 0 | 100 | dround | -round | 1 | 14 | f | t | t
| t
| 1 | f | 1700 | 1700 | 100 |
0 | 0 | 100 | select round($1,0) | -round | 1 | 12 | f | t | t
| t
| 2 | f | 1700 | 1700 23 | 100 |
0 | 0 | 100 | numeric_round | -
(3 rows)
test=# select round(2.5);round
------- 2
(1 row)
test=# select round(2.5,0);round
------- 3
(1 row)
test=# select round(2.5::numeric);round
------- 3
(1 row)
When doing "select round(2.5)" the 2.5 gets cast as a float and the "dround"
function is used. When doing "select round(2.5,0)", or "select
round(2.5::numeric)", the 2.5 gets cast as a numeric and the function
"numeric_round" is used, producing a different result. It looks like
"dround" simply calls the rint system function, so I'd guess the issue is
really there (and maybe platform dependent?). I do recall at least one
interpretation of rounding that calls for rounding a 5 to the even digit
(ASTM), so the rint behavior may not be strictly speaking a bug -- but
certainly having two different interpretations is.
In any case, use "select round(2.5,0)" for now.
Hope this helps,
Joe
