Thread: Significant Digits in Floating Point Datatype
I would like to store some in a single array some data that is conceptually related, but some of the data is floating point, and some of it is integer. Obviously the integer data *can* be stored as double precision, but I need to know about potential loss of precision. Double precision has "a precision of at least 15 digits." I would assume that that would mean that for double precision, 15 digits of data would be faithfully preserved. But I found a question on the list where a value stored as 955.60 sometimes returns as 955.599999999998. (http://archives.postgresql.org/pgsql-general/2011-08/msg00144.php) If this is the case, what does "a precision of at least [x] digits" actually mean? And can I reliably retrieve the original integer by casting to int (or bigint) if the number of digits in the original integer is less than 15? Regards, --Lee -- Lee Hachadoorian PhD, Earth& Environmental Sciences (Geography) Research Associate, CUNY Center for Urban Research http://freecity.commons.gc.cuny.edu
Lee Hachadoorian <lee.hachadoorian@gmail.com> writes:
> And can I reliably retrieve the original integer by
> casting to int (or bigint) if the number of digits in the original
> integer is less than 15?
On IEEE-floating-point machines, I'd expect float8 to store integers
up to 2^52 (or maybe it's 2^53) exactly. With other floating-point
formats the limit might be different, but it should still be exact for
reasonable-size integers. This has nothing whatever to do with whether
decimal fractions are reproduced exactly (in general, they aren't, no
matter how many or few digits are involved). So integers are fine,
bigints not so much.
regards, tom lane
On 11/20/11 1:29:37 PM, Lee Hachadoorian wrote: > I would like to store some in a single array some data that is > conceptually related, but some of the data is floating point, and some > of it is integer. Obviously the integer data *can* be stored as double > precision, but I need to know about potential loss of precision. Double > precision has "a precision of at least 15 digits." I would assume that > that would mean that for double precision, 15 digits of data would be > faithfully preserved. But I found a question on the list where a value > stored as 955.60 sometimes returns as 955.599999999998. > (http://archives.postgresql.org/pgsql-general/2011-08/msg00144.php) If > this is the case, what does "a precision of at least [x] digits" > actually mean? And can I reliably retrieve the original integer by > casting to int (or bigint) if the number of digits in the original > integer is less than 15? Given Tom's answer, you may want to consider whether the DECIMAL data type is a better fit for your needs. -- Bill Moran
On 11/20/2011 02:06 PM, Tom Lane wrote: > Lee Hachadoorian<lee.hachadoorian@gmail.com> writes: >> And can I reliably retrieve the original integer by >> casting to int (or bigint) if the number of digits in the original >> integer is less than 15? > On IEEE-floating-point machines, I'd expect float8 to store integers > up to 2^52 (or maybe it's 2^53) exactly. With other floating-point > formats the limit might be different, but it should still be exact for > reasonable-size integers. This has nothing whatever to do with whether > decimal fractions are reproduced exactly (in general, they aren't, no > matter how many or few digits are involved). So integers are fine, > bigints not so much. > > regards, tom lane Thank you, that clarification is extremely useful. --Lee -- Lee Hachadoorian PhD, Earth& Environmental Sciences (Geography) Research Associate, CUNY Center for Urban Research http://freecity.commons.gc.cuny.edu