Re: Postgres 9.2.4 "Double Precision" Precision

Arian,

It is a single database . Postgres was upgraded from 8.2.6 to 9.2.4... the
database contents was not altered in upgrade, that is a 8.2.6 dump was not
created then uploaded to 9.2.4.

Perhaps handling of double precision, changed from 8.2.6 to 9.2.4 ?

Joanne

On Fri, Sep 13, 2013 at 11:40 AM, Adrian Klaver <adrian(dot)klaver(at)gmail(dot)com>wrote:

> On 09/13/2013 11:32 AM, NWRFC Portland wrote:
>
>> I recently upgraded from postgres 8.2.6 to 9.2.4 . For the most part I
>> am enjoying the upgrade. I have found one behavior that I can not
>> explain.....
>>
>> Below is sample contents of a table. "VALUE" in column 7 is defined as
>> double precision (table definition is the same in 8.2.6 as 9.2.4).
>> The change over from 8.2.6 to 9.2.4 happened after 2013-09-10 16:00:00.
>>
>> In postgres 8.2.6 a value inserted , 6.31, is represented in the
>> database as 6.31 (7th column,7th row)
>> In postgres 9.2.4 a value inserted , 6.32, is represented in the
>> database as 6.32000017166138 (7th column, 6th row)
>>
>> VALUE
>> SLMO3 | HG | 0 | RG | Z | 2013-09-10 18:15:00 |
>> 6.32000017166138 | Z | 1879048191 | 0 | MSGPRODID
>> | 2013-09-10 20:02:00 | 2013-09-10 20:03:15
>> SLMO3 | HG | 0 | RP | Z | 2013-09-10 18:15:00 |
>> 6.32999992370605 | Z | 1879048191 | 0 | KPQRRR6PQR
>> | 2013-09-10 19:15:00 | 2013-09-10 19:15:34
>> SLMO3 | HG | 0 | RP | Z | 2013-09-10 18:00:00 |
>> 6.32999992370605 | Z | 1879048191 | 0 | KPQRRR6PQR
>> | 2013-09-10 18:18:00 | 2013-09-10 18:18:12
>> SLMO3 | HG | 0 | RP | Z | 2013-09-10 17:45:00 |
>> 6.32999992370605 | Z | 1879048191 | 0 | KPQRRR6PQR
>> | 2013-09-10 18:18:00 | 2013-09-10 18:18:12
>> SLMO3 | HG | 0 | RP | Z | 2013-09-10 17:30:00 |
>> 6.32000017166138 | Z | 1879048191 | 0 | KPQRRR6PQR
>> | 2013-09-10 18:18:00 | 2013-09-10 18:18:12
>> SLMO3 | HG | 0 | RP | Z | 2013-09-10 17:15:00 |
>> 6.32000017166138 | Z | 1879048191 | 0 | KPQRRR6PQR
>> | 2013-09-10 18:18:00 | 2013-09-10 18:18:12
>> SLMO3 | HG | 0 | RP | Z | 2013-09-10 16:00:00 |
>> 6.31 | Z | 1879048191 | 0 | KPQRRR6PQR |
>> 2013-09-10 16:18:23 | 2013-09-10 16:18:23
>> SLMO3 | HG | 0 | RG | Z | 2013-09-10 16:00:00 |
>> 6.28 | Z | 1879048191 | 0 | KWOHRRSPTR |
>> 2013-09-10 16:16:32 | 2013-09-10 16:17:33
>> SLMO3 | HG | 0 | RG | Z | 2013-09-10 15:45:00 |
>> 6.28 | Z | 1879048191 | 0 | KWOHRRSPTR |
>> 2013-09-10 16:16:32 | 2013-09-10 16:17:33
>> SLMO3 | HG | 0 | RP | Z | 2013-09-10 15:45:00 |
>> 6.31 | Z | 1879048191 | 0 | KPQRRR6PQR |
>> 2013-09-10 16:18:23 | 2013-09-10 16:18:23
>> SLMO3 | HG | 0 | RP | Z | 2013-09-10 15:30:00 |
>> 6.31 | Z | 1879048191 | 0 | KPQRRR6PQR |
>> 2013-09-10 16:18:23 | 2013-09-10 16:18:23
>> SLMO3 | HG | 0 | RG | Z | 2013-09-10 15:30:00 |
>> 6.27 | Z | 1879048191 | 0 | KWOHRRSPTR |
>> 2013-09-10 16:16:32 | 2013-09-10 16:17:33
>>
>> As the values effect arithmetic calculations, I wish the database to
>> represent values as they are given. For instance, 6.345 would be
>> represented as 6.345..... 6.3 represented as 6.3.
>> Does anyone have any suggestions?
>>
>
> Are the two databases on the same machine?
>
> If you want defined precision you will need to use numeric, see docs below
> for more information.
>
> http://www.postgresql.org/**docs/9.2/interactive/datatype-**numeric.html<http://www.postgresql.org/docs/9.2/interactive/datatype-numeric.html>
>
> 8.1.3. Floating-Point Types
>
> The data types real and double precision are inexact, variable-precision
> numeric types. In practice, these types are usually implementations of IEEE
> Standard 754 for Binary Floating-Point Arithmetic (single and double
> precision, respectively), to the extent that the underlying processor,
> operating system, and compiler support it.
>
> Inexact means that some values cannot be converted exactly to the internal
> format and are stored as approximations, so that storing and retrieving a
> value might show slight discrepancies. Managing these errors and how they
> propagate through calculations is the subject of an entire branch of
> mathematics and computer science and will not be discussed here, except for
> the following points:
>
> If you require exact storage and calculations (such as for monetary
> amounts), use the numeric type instead.
>
> If you want to do complicated calculations with these types for anything
> important, especially if you rely on certain behavior in boundary cases
> (infinity, underflow), you should evaluate the implementation carefully.
>
> Comparing two floating-point values for equality might not always work as
> expected.
>
> On most platforms, the real type has a range of at least 1E-37 to 1E+37
> with a precision of at least 6 decimal digits. The double precision type
> typically has a range of around 1E-307 to 1E+308 with a precision of at
> least 15 digits. Values that are too large or too small will cause an
> error. Rounding might take place if the precision of an input number is too
> high. Numbers too close to zero that are not representable as distinct from
> zero will cause an underflow error.
>
>
>
>
>> Thanks in advance,
>> Joanne
>>
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general>
>

--

Joanne R. Salerno
Sr Hydrologist

503.326.7291
NOAA/Northwest River Forecast Center
Portland, OR