Thread: numeric and float converts to int differently?
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 -------------------------------
"SZŰCS Gábor" <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
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 -------------------------------
"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
----- 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 -------------------------------
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.
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)