pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feednumeric and float converts to int differently?
7+ messages / 6 participants
[nested] [flat]
* numeric and float converts to int differently?
@ 2003-10-28 15:08 SZŰCS Gábor <surrano@mailbox.hu>
0 siblings, 2 replies; 7+ messages in thread
From: SZŰCS Gábor @ 2003-10-28 15:08 UTC (permalink / raw)
To: pgsql-sql
Dear Gurus,
I'm using PostgreSQL 7.3.3.
I was wondering if this is undefined, intentional or to be changed/fixed in
the future. See the snippit at the end of this mail.
QUESTION 1: Is it intentional that converting 0.5 to int4
- from numeric: rounds *away from* zero
- from float: rounds *towards* zero (tried float4 and float8 too)?
QUESTION 2: Is it safe to assume it won't change (haven't changed) in the
upcoming versions?
QUESTION 3: Is there a function or conversion method that converts 0.5
according to
- mathematical rules (*up*)
- accounting rules (*down*, at least here in Hungary)?
TIA,
G.
------------------------------- cut here -------------------------------
# select int4('0.5'::numeric);
1
# select int4('-0.5'::numeric);
-1
# select int4('0.5'::float);
0
# select int4('-0.5'::float);
0
------------------------------- cut here -------------------------------
What I would really like to see:
# select round_math('0.5');
1
# select round_math('-0.5');
0
# select round_acct('0.5');
0
# select round_acct('-0.5');
-1
------------------------------- cut here -------------------------------
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: numeric and float converts to int differently?
@ 2003-10-28 16:26 Tom Lane <tgl@sss.pgh.pa.us>
parent: SZŰCS Gábor <surrano@mailbox.hu>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2003-10-28 16:26 UTC (permalink / raw)
To: SZŰCS Gábor <surrano@mailbox.hu>; +Cc: pgsql-sql
"=?iso-8859-2?B?U1rbQ1MgR+Fib3I=?=" <surrano@mailbox.hu> writes:
> QUESTION 1: Is it intentional that converting 0.5 to int4
> - from numeric: rounds *away from* zero
> - from float: rounds *towards* zero (tried float4 and float8 too)?
Numeric currently rounds away from zero. We cannot guarantee to make
it work the same as float, because float's rounding behavior is
platform-dependent. A common but not universal behavior is "round to
nearest even integer". See the recent thread about the round() function
(I forget which list it was in, but it was within the past week).
> QUESTION 2: Is it safe to assume it won't change (haven't changed) in the
> upcoming versions?
Numeric's behavior hasn't changed as of 7.4beta5. I do not have a
crystal ball to predict whether we might change it in the future.
I doubt we will ever try to override the platform behavior for float.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: numeric and float converts to int differently?
@ 2003-10-28 16:42 SZŰCS Gábor <surrano@mailbox.hu>
parent: SZŰCS Gábor <surrano@mailbox.hu>
1 sibling, 1 reply; 7+ messages in thread
From: SZŰCS Gábor @ 2003-10-28 16:42 UTC (permalink / raw)
To: pgsql-sql
Dear Ken,
reply goes to the list if you don't mind :)
----- Original Message -----
From: "Kenneth Marshall" <ktm@it.is.rice.edu>
To: "SZŰCS Gábor" <surrano@mailbox.hu>
Sent: Tuesday, October 28, 2003 4:45 PM
> Postgres is using the standard definition of rounding. What you
The "standard" definition I know states that 0.5 should round *up*, not
*away from zero*. i.e. 3.5 rounds (up) to 4, and -3.5 rounds (up) to -3. I
was just wondering if there is an explicit/official claim that Postgres does
round away from zero.
> cannot see is that the float values are not actually exactly 0.5
Yes I could guess that (floating point vs fixed), but is this a coincidence
that both '0.5'::float and '-0.5'::float are closer to 0, whereas they could
be closer to +/-1, as well as both closer to the lower or upper bound.
This is why I asked if it's intentional/guaranteed, or undefined -- say, a
future change in glibc may cause this to change.
> I cannot imagine that the behavior would ever change.
If you can tell the developers' opinion for sure, that's enough for me :)
G.
------------------------------- cut here -------------------------------
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: numeric and float converts to int differently?
@ 2003-10-28 18:21 Greg Stark <gsstark@mit.edu>
parent: SZŰCS Gábor <surrano@mailbox.hu>
0 siblings, 1 reply; 7+ messages in thread
From: Greg Stark @ 2003-10-28 18:21 UTC (permalink / raw)
To: SZÛCS Gábor <surrano@mailbox.hu>; +Cc: pgsql-sql
"SZÛCS Gábor" <surrano@mailbox.hu> writes:
> > cannot see is that the float values are not actually exactly 0.5
>
> Yes I could guess that (floating point vs fixed), but is this a coincidence
> that both '0.5'::float and '-0.5'::float are closer to 0, whereas they could
> be closer to +/-1, as well as both closer to the lower or upper bound.
Wouldn't 0.5 and -0.5 be representable exactly as floats?
--
greg
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: numeric and float converts to int differently?
@ 2003-10-28 20:16 SZUCS Gábor <surrano@mailbox.hu>
parent: Greg Stark <gsstark@mit.edu>
0 siblings, 2 replies; 7+ messages in thread
From: SZUCS Gábor @ 2003-10-28 20:16 UTC (permalink / raw)
To: pgsql-sql
----- Original Message -----
From: "Greg Stark" <gsstark@mit.edu>
Sent: Tuesday, October 28, 2003 7:21 PM
> > > cannot see is that the float values are not actually exactly 0.5
> >
> > Yes I could guess that (floating point vs fixed), but is this a
coincidence
> > that both '0.5'::float and '-0.5'::float are closer to 0, whereas they
could
> > be closer to +/-1, as well as both closer to the lower or upper bound.
>
> Wouldn't 0.5 and -0.5 be representable exactly as floats?
As Tom pointed out, it isn't a floating point failure -- it is how rounding
float is implemented. I assume anything with less than 15 digits can be
exactly represented as float.
G.
------------------------------- cut here -------------------------------
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: numeric and float converts to int differently?
@ 2003-10-28 20:31 Bruno Wolff III <bruno@wolff.to>
parent: SZUCS Gábor <surrano@mailbox.hu>
1 sibling, 0 replies; 7+ messages in thread
From: Bruno Wolff III @ 2003-10-28 20:31 UTC (permalink / raw)
To: SZUCS Gábor <surrano@mailbox.hu>; +Cc: pgsql-sql
On Tue, Oct 28, 2003 at 21:16:53 +0100,
SZUCS Gábor <surrano@mailbox.hu> wrote:
>
> As Tom pointed out, it isn't a floating point failure -- it is how rounding
> float is implemented. I assume anything with less than 15 digits can be
> exactly represented as float.
No. It has to be a dyadic number. For example .3 will not be represented
exactly by a float.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: numeric and float converts to int differently?
@ 2003-10-28 23:11 Christopher Browne <cbbrowne@libertyrms.info>
parent: SZUCS Gábor <surrano@mailbox.hu>
1 sibling, 0 replies; 7+ messages in thread
From: Christopher Browne @ 2003-10-28 23:11 UTC (permalink / raw)
To: pgsql-sql
surrano@mailbox.hu (SZUCS Gábor) writes:
> As Tom pointed out, it isn't a floating point failure -- it is how rounding
> float is implemented. I assume anything with less than 15 digits can be
> exactly represented as float.
No, "decimal" fractions cannot ever be exactly represented in floating
point because since they use powers of two, you wind up with repeated
fractions.
1/3 is approximately 0.3333333, but you cannot present that exactly in
decimal.
In the very same way, 1/10 is approximately equal to
0.001001001001001001001001001001 (as a binary 'fraction'); the '001'
part is a repeating group, and wherever you terminate it, you lose
exactness.
--
(reverse (concatenate 'string "ofni.smrytrebil" "@" "enworbbc"))
<http://dev6.int.libertyrms.com/;
Christopher Browne
(416) 646 3304 x124 (land)
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2003-10-28 23:11 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-28 15:08 numeric and float converts to int differently? SZŰCS Gábor <surrano@mailbox.hu>
2003-10-28 16:26 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-10-28 16:42 ` SZŰCS Gábor <surrano@mailbox.hu>
2003-10-28 18:21 ` Greg Stark <gsstark@mit.edu>
2003-10-28 20:16 ` SZUCS Gábor <surrano@mailbox.hu>
2003-10-28 20:31 ` Bruno Wolff III <bruno@wolff.to>
2003-10-28 23:11 ` Christopher Browne <cbbrowne@libertyrms.info>
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