From: Joe Conway <joseph.conway@home.com>
To: Saurabh Mittal <lattim@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: ROUND function ??
Date: Mon, 8 Oct 2001 22:03:50 -0700
Message-ID: <003a01c1507f$c99cf520$0205a8c0@jecw2k1> (raw)
References: <OE25NZwwPcMFlF8HGmC0000b0ea@hotmail.com>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: joseph.conway@home.com, lattim@hotmail.com
Subject: Re: ROUND function ??
In-Reply-To: <003a01c1507f$c99cf520$0205a8c0@jecw2k1>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox