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.kla...@gmail.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.kla...@gmail.com > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.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
