Received: from mail1.hub.org (webmail.hub.org [216.126.85.1]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f9954on93583 for ; Tue, 9 Oct 2001 01:04:51 -0400 (EDT) (envelope-from joseph.conway@home.com) Received: from femail3.sdc1.sfba.home.com (femail3.sdc1.sfba.home.com [24.0.95.83]) by mail1.hub.org (8.11.3/8.11.4) with ESMTP id f9953lX93517 for ; Tue, 9 Oct 2001 01:03:47 -0400 (EDT) (envelope-from joseph.conway@home.com) Received: from jecw2k1 ([24.0.42.111]) by femail3.sdc1.sfba.home.com (InterMail vM.4.01.03.20 201-229-121-120-20010223) with SMTP id <20011009050344.TVEB18284.femail3.sdc1.sfba.home.com@jecw2k1>; Mon, 8 Oct 2001 22:03:44 -0700 Message-ID: <003a01c1507f$c99cf520$0205a8c0@jecw2k1> From: "Joe Conway" To: "Saurabh Mittal" , References: Subject: Re: ROUND function ?? Date: Mon, 8 Oct 2001 22:03:50 -0700 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4807.1700 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4807.1700 X-Archive-Number: 200110/130 X-Sequence-Number: 5085 > 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