Re: ROUND function ??

> 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