Thread: Postgres 9.2.4 "Double Precision" Precision
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?
Thanks in advance,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?
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
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.klaver@gmail.com> wrote:
Are the two databases on the same machine?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?
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
Joanne R. Salerno Sr Hydrologist 503.326.7291 NOAA/Northwest River Forecast Center Portland, OR
On 09/13/2013 12:36 PM, Joanne Salerno - NOAA Federal wrote: > 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. So you used pg_upgrade to move the data? If not you can't move from one major version to another without a dump/restore. > > Perhaps handling of double precision, changed from 8.2.6 to 9.2.4 ? > > Joanne > -- Adrian Klaver adrian.klaver@gmail.com
On 09/13/2013 12:36 PM, Joanne Salerno - NOAA Federal wrote: > 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 ? To follow up, I don't think that is the case. By way of example, granted on a 9.3 instance: create table float_test (id int, f_fld double precision); insert into float_test values (1, 6.31); insert into float_test values (2, 6.32); select * from float_test ; id | f_fld ----+------- 1 | 6.31 2 | 6.32 (2 rows) A couple of questions: How is the data entered into the database? Just wondering if there was any client side changes along with the database change? > > Joanne -- Adrian Klaver adrian.klaver@gmail.com
Adrian Klaver <adrian.klaver@gmail.com> wrote: > create table float_test (id int, f_fld double precision); > insert into float_test values (1, 6.31); > insert into float_test values (2, 6.32); > select * from float_test ; > > id | f_fld > ----+------- > 1 | 6.31 > 2 | 6.32 > (2 rows) If, instead of those inserts I use these: insert into float_test values (1, '6.31'); insert into float_test values (1, '6.32'); insert into float_test values (1, '6.32'::double precision); insert into float_test values (1, '6.32'::real); I get: id | f_fld ----+------------------ 1 | 6.31 1 | 6.32 1 | 6.32 1 | 6.32000017166138 (4 rows) Apparently the value is being treated as a real value somewhere. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
On 09/14/2013 08:51 AM, Kevin Grittner wrote: > Adrian Klaver <adrian.klaver@gmail.com> wrote: > >> create table float_test (id int, f_fld double precision); >> insert into float_test values (1, 6.31); >> insert into float_test values (2, 6.32); >> select * from float_test ; >> >> id | f_fld >> ----+------- >> 1 | 6.31 >> 2 | 6.32 >> (2 rows) > > If, instead of those inserts I use these: > > insert into float_test values (1, '6.31'); > insert into float_test values (1, '6.32'); > insert into float_test values (1, '6.32'::double precision); > insert into float_test values (1, '6.32'::real); > > I get: > > id | f_fld > ----+------------------ > 1 | 6.31 > 1 | 6.32 > 1 | 6.32 > 1 | 6.32000017166138 > (4 rows) > > Apparently the value is being treated as a real value somewhere. Interesting, more grist for the mill. > > -- > Kevin Grittner > EDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > > -- Adrian Klaver adrian.klaver@gmail.com
Adrian, Kevin,
Thank you for the clues. It turns out a java process was added (between the data source and database) at same time of postgres upgrade. It was the java process that incorrectly handled the double precision data.
Joanne
On Sat, Sep 14, 2013 at 9:57 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
Interesting, more grist for the mill. Adrian KlaverOn 09/14/2013 08:51 AM, Kevin Grittner wrote:Adrian Klaver <adrian.klaver@gmail.com> wrote:create table float_test (id int, f_fld double precision);
insert into float_test values (1, 6.31);
insert into float_test values (2, 6.32);
select * from float_test ;
id | f_fld
----+-------
1 | 6.31
2 | 6.32
(2 rows)
If, instead of those inserts I use these:
insert into float_test values (1, '6.31');
insert into float_test values (1, '6.32');
insert into float_test values (1, '6.32'::double precision);
insert into float_test values (1, '6.32'::real);
I get:
id | f_fld
----+------------------
1 | 6.31
1 | 6.32
1 | 6.32
1 | 6.32000017166138
(4 rows)
Apparently the value is being treated as a real value somewhere.
adrian.klaver@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