Significant Digits in Floating Point Datatype

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