Re: Postgres 9.2.4 "Double Precision" Precision - Mailing list pgsql-general

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

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.klaver@gmail.com