pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPossible rounding error of large float values?
7+ messages / 6 participants
[nested] [flat]
* Possible rounding error of large float values?
@ 2004-08-17 17:21 Adam Lancaster <alancaster@preventsys.com>
0 siblings, 1 reply; 7+ messages in thread
From: Adam Lancaster @ 2004-08-17 17:21 UTC (permalink / raw)
To: pgsql-sql
When setting a float column to this value:
9223372036854775807
It gets selected out as:
9.22337203685478E18
Which appears to be rounded. When we cast it to numeric type we get:
9223372036854780000
Which also is rounded. It is still possible to find the row using the
original value
select * from <table> where <col> = 9223372036854775807
Is this expected behavior?
Thanks,
Adam
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Possible rounding error of large float values?
@ 2004-08-24 19:52 Josh Berkus <josh@agliodbs.com>
parent: Adam Lancaster <alancaster@preventsys.com>
0 siblings, 1 reply; 7+ messages in thread
From: Josh Berkus @ 2004-08-24 19:52 UTC (permalink / raw)
To: Adam Lancaster <alancaster@preventsys.com>; pgsql-sql
Adam,
> 9223372036854775807
>
> It gets selected out as:
>
> 9.22337203685478E18
This is a property of FLOAT data types. They round.
> Which appears to be rounded. When we cast it to numeric type we get:
>
> 9223372036854780000
>
> Which also is rounded. It is still possible to find the row using the
> original value
Hmmm ... is 15 digits the limit of NUMERIC? It may be.
> Is this expected behavior?
Yes.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Possible rounding error of large float values?
@ 2004-08-24 20:21 Oliver Elphick <olly@lfix.co.uk>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 7+ messages in thread
From: Oliver Elphick @ 2004-08-24 20:21 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Adam Lancaster <alancaster@preventsys.com>; pgsql-sql
On Tue, 2004-08-24 at 20:52, Josh Berkus wrote:
> Adam,
>
> > 9223372036854775807
> >
> > It gets selected out as:
> >
> > 9.22337203685478E18
>
> This is a property of FLOAT data types. They round.
>
> > Which appears to be rounded. When we cast it to numeric type we get:
> >
> > 9223372036854780000
> >
> > Which also is rounded. It is still possible to find the row using the
> > original value
>
> Hmmm ... is 15 digits the limit of NUMERIC? It may be.
It must be the limit of float. Numeric can't produce any more than it
was given and it was cast from float.
bray=# select 653596708775675750507850507570708696432 ::numeric;
numeric
-----------------------------------------
653596708775675750507850507570708696432
(1 row)
--
Oliver Elphick olly@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
"I saw in the night visions, and, behold, one like the
Son of man came with the clouds of heaven, and came to
the Ancient of days, and they brought him near before
him. And there was given him dominion, and glory, and
a kingdom, that all people, nations, and languages,
should serve him; his dominion is an everlasting
dominion, which shall not pass away, and his kingdom
that which shall not be destroyed."
Daniel 7:13,14
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Possible rounding error of large float values?
@ 2004-08-25 00:39 Jan Wieck <JanWieck@Yahoo.com>
parent: Oliver Elphick <olly@lfix.co.uk>
1 sibling, 0 replies; 7+ messages in thread
From: Jan Wieck @ 2004-08-25 00:39 UTC (permalink / raw)
To: olly@lfix.co.uk; +Cc: Josh Berkus <josh@agliodbs.com>; Adam Lancaster <alancaster@preventsys.com>; pgsql-sql
On 8/24/2004 4:21 PM, Oliver Elphick wrote:
> On Tue, 2004-08-24 at 20:52, Josh Berkus wrote:
>> Adam,
>>
>> > 9223372036854775807
>> >
>> > It gets selected out as:
>> >
>> > 9.22337203685478E18
>>
>> This is a property of FLOAT data types. They round.
>>
>> > Which appears to be rounded. When we cast it to numeric type we get:
>> >
>> > 9223372036854780000
>> >
>> > Which also is rounded. It is still possible to find the row using the
>> > original value
>>
>> Hmmm ... is 15 digits the limit of NUMERIC? It may be.
The original numeric code I presented contained even log(), ln() and
trigonometric functions that produced results of up to 1,000 digits
precision. They wheren't considered usefull, so they didn't survive, but
numeric is that good after all.
Jan
>
> It must be the limit of float. Numeric can't produce any more than it
> was given and it was cast from float.
>
> bray=# select 653596708775675750507850507570708696432 ::numeric;
> numeric
> -----------------------------------------
> 653596708775675750507850507570708696432
> (1 row)
>
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Possible rounding error of large float values?
@ 2004-08-25 01:32 Iain <iain@mst.co.jp>
parent: Oliver Elphick <olly@lfix.co.uk>
1 sibling, 1 reply; 7+ messages in thread
From: Iain @ 2004-08-25 01:32 UTC (permalink / raw)
To: Adam Lancaster <alancaster@preventsys.com>; pgsql-sql
# select 9223372036854775807 = 9223372036854775807::float;
?column?
----------
t
(1 row)
# select 9223372036854775807 = 9223372036854775807.0::float;
?column?
----------
t
(1 row)
# select 9223372036854775807 = 9223372036854775807.0000::numeric;
?column?
----------
t
(1 row)
This and the fact that it's still possible to find the row using the
original value would seem to indicate that the rounding is just a display
artifact..
----- Original Message -----
From: "Oliver Elphick" <olly@lfix.co.uk>
To: "Josh Berkus" <josh@agliodbs.com>
Cc: "Adam Lancaster" <alancaster@preventsys.com>; <pgsql-sql@postgresql.org>
Sent: Wednesday, August 25, 2004 5:21 AM
Subject: Re: [SQL] Possible rounding error of large float values?
> On Tue, 2004-08-24 at 20:52, Josh Berkus wrote:
> > Adam,
> >
> > > 9223372036854775807
> > >
> > > It gets selected out as:
> > >
> > > 9.22337203685478E18
> >
> > This is a property of FLOAT data types. They round.
> >
> > > Which appears to be rounded. When we cast it to numeric type we get:
> > >
> > > 9223372036854780000
> > >
> > > Which also is rounded. It is still possible to find the row using the
> > > original value
> >
> > Hmmm ... is 15 digits the limit of NUMERIC? It may be.
>
> It must be the limit of float. Numeric can't produce any more than it
> was given and it was cast from float.
>
> bray=# select 653596708775675750507850507570708696432 ::numeric;
> numeric
> -----------------------------------------
> 653596708775675750507850507570708696432
> (1 row)
>
> --
> Oliver Elphick olly@lfix.co.uk
> Isle of Wight http://www.lfix.co.uk/oliver
> GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
> ========================================
> "I saw in the night visions, and, behold, one like the
> Son of man came with the clouds of heaven, and came to
> the Ancient of days, and they brought him near before
> him. And there was given him dominion, and glory, and
> a kingdom, that all people, nations, and languages,
> should serve him; his dominion is an everlasting
> dominion, which shall not pass away, and his kingdom
> that which shall not be destroyed."
> Daniel 7:13,14
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Possible rounding error of large float values?
@ 2004-08-25 01:41 Stephan Szabo <sszabo@megazone.bigpanda.com>
parent: Iain <iain@mst.co.jp>
0 siblings, 1 reply; 7+ messages in thread
From: Stephan Szabo @ 2004-08-25 01:41 UTC (permalink / raw)
To: Iain <iain@mst.co.jp>; +Cc: Adam Lancaster <alancaster@preventsys.com>; pgsql-sql
On Wed, 25 Aug 2004, Iain wrote:
>
> # select 9223372036854775807 = 9223372036854775807::float;
> ?column?
> ----------
> t
> (1 row)
> This and the fact that it's still possible to find the row using the
> original value would seem to indicate that the rounding is just a display
> artifact..
I don't think that's true since the number ending in 807 is equal to a
version ending in 808.
sszabo=# select 9223372036854775807 = 9223372036854775808::float;
?column?
----------
t
(1 row)
(In this next one I'm casting both because I want to choose the types of
both literals)
sszabo=# select 9223372036854775807::float =
9223372036854775808::numeric;
?column?
----------
t
(1 row)
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Possible rounding error of large float values?
@ 2004-08-25 02:48 Iain <iain@mst.co.jp>
parent: Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 0 replies; 7+ messages in thread
From: Iain @ 2004-08-25 02:48 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Adam Lancaster <alancaster@preventsys.com>; pgsql-sql
> I don't think that's true since the number ending in 807 is equal to a
> version ending in 808.
>
> sszabo=# select 9223372036854775807 = 9223372036854775808::float;
> ?column?
> ----------
> t
> (1 row)
Good point. I think that the problems with using floats are well documented
(I particulary like the doc that come with IBMs java.lang.BigDecimal
package) so there shouldn't be any excuse for being surprised by how they
behave. Basically floats are fine when used for what they were desgned for,
but a liability otherwise.
Just thinking about it now, I can't remember last time I designed a database
that used float data...
regards
Iain
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2004-08-25 02:48 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-17 17:21 Possible rounding error of large float values? Adam Lancaster <alancaster@preventsys.com>
2004-08-24 19:52 ` Josh Berkus <josh@agliodbs.com>
2004-08-24 20:21 ` Oliver Elphick <olly@lfix.co.uk>
2004-08-25 00:39 ` Jan Wieck <JanWieck@Yahoo.com>
2004-08-25 01:32 ` Iain <iain@mst.co.jp>
2004-08-25 01:41 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-08-25 02:48 ` Iain <iain@mst.co.jp>
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