Обсуждение: [ADMIN] data export has strange decimal values
I’m using postgresql9.2. When I export some data using phppgadmin some decimal values are exported as 9.4700003 instead of 9.47 (they show as 9.47 in phppgadmin and when displayed on the web. Others export as 9.5299997 instead of 9.53 (again they show as 9.53 in phppgadmin and when displayed on the web).
The data type for the field is set as type “real.” Is this data type causing the problem and I should change it to decimal?
What could be creating this plus or minus 0.0000003 to the recorded value?
Thanks
Marc
On Wed, Dec 28, 2016 at 9:41 AM, Marc Fromm <Marc.Fromm@wwu.edu> wrote:
I’m using postgresql9.2. When I export some data using phppgadmin some decimal values are exported as 9.4700003 instead of 9.47 (they show as 9.47 in phppgadmin and when displayed on the web. Others export as 9.5299997 instead of 9.53 (again they show as 9.53 in phppgadmin and when displayed on the web).
The data type for the field is set as type “real.” Is this data type causing the problem and I should change it to decimal?
What could be creating this plus or minus 0.0000003 to the recorded value?
Thanks
Marc
"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..."
See also:
https://www.youtube.com/watch?v=PZRI1IfStY0
Cheers,
Steve
On Wed, Dec 28, 2016 at 12:19 PM, Steve Crawford <scrawford@pinpointresearch.com> wrote: > > > On Wed, Dec 28, 2016 at 9:41 AM, Marc Fromm <Marc.Fromm@wwu.edu> wrote: >> >> I’m using postgresql9.2. When I export some data using phppgadmin some >> decimal values are exported as 9.4700003 instead of 9.47 (they show as 9.47 >> in phppgadmin and when displayed on the web. Others export as 9.5299997 >> instead of 9.53 (again they show as 9.53 in phppgadmin and when displayed on >> the web). >> >> >> >> The data type for the field is set as type “real.” Is this data type >> causing the problem and I should change it to decimal? >> >> >> >> What could be creating this plus or minus 0.0000003 to the recorded value? >> > > > https://www.postgresql.org/docs/9.6/static/datatype-numeric.html > > "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..." Yeah if OP wants exact numbers he needs to use numeric type.
Thanks for the replies. What datatype do I use for exact decimal numbers? Googling datatypes for postgresql several sitesmention datatype decimal, but according to the dropdown menu for altering the field type, there is no "decimal" typeto select, unless it's under some different name. -----Original Message----- From: Scott Marlowe [mailto:scott.marlowe@gmail.com] Sent: Wednesday, December 28, 2016 12:06 PM To: Steve Crawford <scrawford@pinpointresearch.com> Cc: Marc Fromm <Marc.Fromm@wwu.edu>; pgsql-admin@postgresql.org Subject: Re: [ADMIN] data export has strange decimal values On Wed, Dec 28, 2016 at 12:19 PM, Steve Crawford <scrawford@pinpointresearch.com> wrote: > > > On Wed, Dec 28, 2016 at 9:41 AM, Marc Fromm <Marc.Fromm@wwu.edu> wrote: >> >> I’m using postgresql9.2. When I export some data using phppgadmin >> some decimal values are exported as 9.4700003 instead of 9.47 (they >> show as 9.47 in phppgadmin and when displayed on the web. Others >> export as 9.5299997 instead of 9.53 (again they show as 9.53 in >> phppgadmin and when displayed on the web). >> >> >> >> The data type for the field is set as type “real.” Is this data type >> causing the problem and I should change it to decimal? >> >> >> >> What could be creating this plus or minus 0.0000003 to the recorded value? >> > > > https://www.postgresql.org/docs/9.6/static/datatype-numeric.html > > "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..." Yeah if OP wants exact numbers he needs to use numeric type.
>> Yeah if OP wants exact numbers he needs to use numeric type. I see, numeric is the type. Not sure why documentation mentions decimal, yet in phpPgAdmin it doesn’t provide decimal asan option for type. -----Original Message----- From: Marc Fromm Sent: Wednesday, December 28, 2016 4:14 PM To: 'Scott Marlowe' <scott.marlowe@gmail.com>; Steve Crawford <scrawford@pinpointresearch.com> Cc: pgsql-admin@postgresql.org Subject: RE: [ADMIN] data export has strange decimal values Thanks for the replies. What datatype do I use for exact decimal numbers? Googling datatypes for postgresql several sitesmention datatype decimal, but according to the dropdown menu for altering the field type, there is no "decimal" typeto select, unless it's under some different name. -----Original Message----- From: Scott Marlowe [mailto:scott.marlowe@gmail.com] Sent: Wednesday, December 28, 2016 12:06 PM To: Steve Crawford <scrawford@pinpointresearch.com> Cc: Marc Fromm <Marc.Fromm@wwu.edu>; pgsql-admin@postgresql.org Subject: Re: [ADMIN] data export has strange decimal values On Wed, Dec 28, 2016 at 12:19 PM, Steve Crawford <scrawford@pinpointresearch.com> wrote: > > > On Wed, Dec 28, 2016 at 9:41 AM, Marc Fromm <Marc.Fromm@wwu.edu> wrote: >> >> I’m using postgresql9.2. When I export some data using phppgadmin >> some decimal values are exported as 9.4700003 instead of 9.47 (they >> show as 9.47 in phppgadmin and when displayed on the web. Others >> export as 9.5299997 instead of 9.53 (again they show as 9.53 in >> phppgadmin and when displayed on the web). >> >> >> >> The data type for the field is set as type “real.” Is this data type >> causing the problem and I should change it to decimal? >> >> >> >> What could be creating this plus or minus 0.0000003 to the recorded value? >> > > > https://www.postgresql.org/docs/9.6/static/datatype-numeric.html > > "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..." Yeah if OP wants exact numbers he needs to use numeric type.
>> Yeah if OP wants exact numbers he needs to use numeric type.
I see, numeric is the type. Not sure why documentation mentions decimal, yet in phpPgAdmin it doesn’t provide decimal as an option for type.
I'm not personally against the policy of choosing one item out of set of aliases. My perception is that numeric is by far the more common choice of the two SQL standard arbitrary precision numbers. Given people multiple options just begs them to ponder how they might be different.
"The types decimal and numeric are equivalent. Both types are part of the SQL standard."
David J.