Re: Postgres int rounding

From: "Michael Richards" <michael(at)fastmail(dot)ca>
To: tgl(at)sss(dot)pgh(dot)pa(dot)us
Cc: max(at)nino(dot)ru, pgsql-bugs(at)postgresql(dot)org
Subject: Re: Postgres int rounding
Date: 2001-01-26 05:59:10
Message-ID: 3A71122E.000195.13213@frodo.searchcanada.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Is postgres going to use the scientific method of rounding or just
the simple one? Or even make it configurable. As I recall, the
scientific method says that 4.5 should be rounded to 4 and 5.5 should
be rounded to 6. The idea was that even numbers were easier to work
with and rounding all the x.5 numbers up as the common method says
will eventually skew your average. Rounding evens down and odds up
would probably generate a number of bug reports from people who are
not aware of this though...

-Michael

> The fact that 5*27.81*100 != 27.81*100*5 is certainly a
> garden-variety floating-point roundoff error. However, I think
> Max has a fair complaint here: it seems float-to-int8 conversion
> is truncating, not rounding like the other conversions to integer
> do.
>
> regression=# select 4.7::float8::int4;
> ?column?
> ----------
> 5
> (1 row)
>
> regression=# select 4.7::float8::int8;
> ?column?
> ----------
> 4
> (1 row)
>
> Seems to me this is a bug we should fix.

_________________________________________________________________
http://fastmail.ca/ - Fast Free Web Email for Canadians
>From pgsql-bugs-owner(at)postgresql(dot)org Fri Jan 26 04:14:28 2001
Received: from comptechnews.com (cc993546-b.srst1.fl.home.com [24.3.77.52])
by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f0Q9ERq90886
for <pgsql-bugs(at)postgresql(dot)org>; Fri, 26 Jan 2001 04:14:27 -0500 (EST)
(envelope-from reaster(at)comptechnews(dot)com)
Received: from comptechnews (reaster(at)localhost [127.0.0.1])
by comptechnews.com (8.10.2/8.10.2) with SMTP id f0Q9C2313373;
Fri, 26 Jan 2001 04:12:02 -0500
From: "Robert B. Easter" <reaster(at)comptechnews(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: BUG in postgres mathematic
Date: Fri, 26 Jan 2001 04:12:02 -0500
X-Mailer: KMail [version 1.1.99]
Content-Type: text/plain;
charset="iso-8859-1"
Cc: Max Vaschenko <max(at)nino(dot)ru>, pgsql-bugs(at)postgresql(dot)org
References: <3A70011A(dot)B0E14EF7(at)nino(dot)ru> <0101250729000J(dot)08820(at)comptechnews> <28460(dot)980481150(at)sss(dot)pgh(dot)pa(dot)us>
In-Reply-To: <28460(dot)980481150(at)sss(dot)pgh(dot)pa(dot)us>
MIME-Version: 1.0
Message-Id: <0101260412020Q(dot)08820(at)comptechnews>
Content-Transfer-Encoding: 8bit
X-Archive-Number: 200101/156
X-Sequence-Number: 535

Notice how the INT4 rounding is banker's rounding (round to the nearest even
number). That is what we would want the INT8 to do as well, not just a
simple round like I mentioned before. Again, the INT8 shows truncation. I've
been looking around the source code, but I can't see where all this
happens.

reaster=# SELECT 1.5::FLOAT::INT4;
?column?
----------
2
(1 row)

reaster=# SELECT 2.5::FLOAT::INT4;
?column?
----------
2
(1 row)

reaster=# SELECT 1.5::FLOAT::INT8;
?column?
----------
1
(1 row)

reaster=# SELECT 2.5::FLOAT::INT8;
?column?
----------
2
(1 row)

On Thursday 25 January 2001 22:52, Tom Lane wrote:
> "Robert B. Easter" <reaster(at)comptechnews(dot)com> writes:
> > This problem is not specific to Postgres.
>
> The fact that 5*27.81*100 != 27.81*100*5 is certainly a garden-variety
> floating-point roundoff error. However, I think Max has a fair
> complaint here: it seems float-to-int8 conversion is truncating, not
> rounding like the other conversions to integer do.
>
> regression=# select 4.7::float8::int4;
> ?column?
> ----------
> 5
> (1 row)
>
> regression=# select 4.7::float8::int8;
> ?column?
> ----------
> 4
> (1 row)
>
> Seems to me this is a bug we should fix.
>
> regards, tom lane

--
-------- Robert B. Easter reaster(at)comptechnews(dot)com ---------
-- CompTechNews Message Board http://www.comptechnews.com/ --
-- CompTechServ Tech Services http://www.comptechserv.com/ --
---------- http://www.comptechnews.com/~reaster/ ------------

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Peter T Mount 2001-01-26 09:26:16 Re: Re: [INTERFACES] jdbc driver: Support for 'BOOL'
Previous Message Alex Krohn 2001-01-26 05:51:37 Re: select fails on indexed varchars.