public inbox for [email protected]help / color / mirror / Atom feed
Rounding numbers from copy expert 4+ messages / 2 participants [nested] [flat]
* Rounding numbers from copy expert @ 2019-07-24 23:28 John Abraham <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: John Abraham @ 2019-07-24 23:28 UTC (permalink / raw) To: psycopg Hmm, I have two machines connecting to the same database running the same query inside copy_expert. Both seem to have psycopg2 2.7.5 (based on pip3 freeze). One seems to be rounding the double precision number 696538951.985381 to 6.96538944E8 whereas the other machine isn't rounding it. This is causing auditability/repeatability problems, as the difference is actually significant in this context. Is there some thing about the way psycopg2 connects to the database, or the way psycopg2 is configured, that could be causing this rounding? Is there some way to express a preference for scientific notation? I'm at a complete loss as to why copy_expert (which basically does a 'copy to stdin' query) would be different when called from different machines. My next step in the madness is to see if the locale has an effect, one is set to en_CA.UTF-8 and the other is set to en_US.UTF-8. But, perhaps someone else has a better idea. Thanks for any help. -- John Abraham [email protected] 403-232-1060 ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Rounding numbers from copy expert @ 2019-07-24 23:33 Adrian Klaver <[email protected]> parent: John Abraham <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: Adrian Klaver @ 2019-07-24 23:33 UTC (permalink / raw) To: John Abraham <[email protected]>; psycopg On 7/24/19 4:28 PM, John Abraham wrote: > Hmm, I have two machines connecting to the same database running the > same query inside copy_expert. > > Both seem to have psycopg2 2.7.5 (based on pip3 freeze). > > One seems to be rounding the double precision number 696538951.985381 to > 6.96538944E8 whereas the other machine isn't rounding it. First: https://www.postgresql.org/docs/11/datatype-numeric.html#DATATYPE-FLOAT 8.1.3. Floating-Point Types "The data types real and double precision are inexact, variable-precision numeric types. ... 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: ..." Second: Where are you actually 'looking' at the values? > > This is causing auditability/repeatability problems, as the difference > is actually significant in this context. > > Is there some thing about the way psycopg2 connects to the database, or > the way psycopg2 is configured, that could be causing this rounding? Is > there some way to express a preference for scientific notation? > > I'm at a complete loss as to why copy_expert (which basically does a > 'copy to stdin' query) would be different when called from different > machines. My next step in the madness is to see if the locale has an > effect, one is set to en_CA.UTF-8 and the other is set to en_US.UTF-8. > But, perhaps someone else has a better idea. > > Thanks for any help. > > -- > John Abraham > [email protected] <mailto:[email protected]> > 403-232-1060 > -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Rounding numbers from copy expert @ 2019-07-25 00:35 John Abraham <[email protected]> parent: Adrian Klaver <[email protected]> 0 siblings, 1 reply; 4+ messages in thread From: John Abraham @ 2019-07-25 00:35 UTC (permalink / raw) To: Adrian Klaver <[email protected]>; +Cc: psycopg > On Jul 24, 2019, at 4:33 PM, Adrian Klaver <[email protected]> wrote: > >> On 7/24/19 4:28 PM, John Abraham wrote: >> Hmm, I have two machines connecting to the same database running the same query inside copy_expert. >> Both seem to have psycopg2 2.7.5 (based on pip3 freeze). >> One seems to be rounding the double precision number 696538951.985381 to 6.96538944E8 whereas the other machine isn't rounding it. > > First: > > https://www.postgresql.org/docs/11/datatype-numeric.html#DATATYPE-FLOAT > > 8.1.3. Floating-Point Types > "The data types real and double precision are inexact, variable-precision numeric types. ... > > 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: ..." One of my proudest moments in my very first job in the late 80s was solving in just a few minutes a lack-of-precision problem my boss was fighting with for more than a day because he was subtracting two similar floating point numbers. I’m enjoying the irony of fighting a similar problem now, myself, 30 years later. Anyway, in this case it’s not the precision, it’s the repeatability. If you run the same program twice on two computers with the same software, you *should* get *exactly* the same answer. > > Second: > > Where are you actually 'looking' at the values? I’m seeing the difference in a .CSV file produced by copy_expert, which as far as I can tell just streams the stdout from a database ‘copy to stdout’ command to a text file. I’m trying to figure out why the ‘copy to stdout’ SQL command would give different interpretations of a number. > > >> This is causing auditability/repeatability problems, as the difference is actually significant in this context. >> Is there some thing about the way psycopg2 connects to the database, or the way psycopg2 is configured, that could be causing this rounding? Is there some way to express a preference for scientific notation? >> I'm at a complete loss as to why copy_expert (which basically does a 'copy to stdin' query) would be different when called from different machines. My next step in the madness is to see if the locale has an effect, one is set to en_CA.UTF-8 and the other is set to en_US.UTF-8. But, perhaps someone else has a better idea. >> Thanks for any help. >> -- >> John Abraham >> [email protected] <mailto:[email protected]> >> 403-232-1060 > > > -- > Adrian Klaver > [email protected] — John Abraham ^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Rounding numbers from copy expert @ 2019-07-25 00:48 Adrian Klaver <[email protected]> parent: John Abraham <[email protected]> 0 siblings, 0 replies; 4+ messages in thread From: Adrian Klaver @ 2019-07-25 00:48 UTC (permalink / raw) To: John Abraham <[email protected]>; +Cc: psycopg On 7/24/19 5:35 PM, John Abraham wrote: > >> On Jul 24, 2019, at 4:33 PM, Adrian Klaver <[email protected]> wrote: >> >>> On 7/24/19 4:28 PM, John Abraham wrote: >>> Hmm, I have two machines connecting to the same database running the same query inside copy_expert. >>> Both seem to have psycopg2 2.7.5 (based on pip3 freeze). >>> One seems to be rounding the double precision number 696538951.985381 to 6.96538944E8 whereas the other machine isn't rounding it. >> >> First: >> >> https://www.postgresql.org/docs/11/datatype-numeric.html#DATATYPE-FLOAT >> >> 8.1.3. Floating-Point Types >> "The data types real and double precision are inexact, variable-precision numeric types. ... >> >> 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: ..." > > > One of my proudest moments in my very first job in the late 80s was solving in just a few minutes a lack-of-precision problem my boss was fighting with for more than a day because he was subtracting two similar floating point numbers. I’m enjoying the irony of fighting a similar problem now, myself, 30 years later. > > Anyway, in this case it’s not the precision, it’s the repeatability. If you run the same program twice on two computers with the same software, you *should* get *exactly* the same answer. > >> >> Second: >> >> Where are you actually 'looking' at the values? > > I’m seeing the difference in a .CSV file produced by copy_expert, which as far as I can tell just streams the stdout from a database ‘copy to stdout’ command to a text file. I’m trying to figure out why the ‘copy to stdout’ SQL command would give different interpretations of a number. Not sure that it makes a difference, but in your OP you said you where doing: "... why copy_expert (which basically does a 'copy to stdin' query) ..." So how similar/dissimilar are the two computers you are doing this on?: 1) OS and version? 2) Machine architecture? 3) Python version and build? > >> >> >>> This is causing auditability/repeatability problems, as the difference is actually significant in this context. >>> Is there some thing about the way psycopg2 connects to the database, or the way psycopg2 is configured, that could be causing this rounding? Is there some way to express a preference for scientific notation? >>> I'm at a complete loss as to why copy_expert (which basically does a 'copy to stdin' query) would be different when called from different machines. My next step in the madness is to see if the locale has an effect, one is set to en_CA.UTF-8 and the other is set to en_US.UTF-8. But, perhaps someone else has a better idea. >>> Thanks for any help. >>> -- >>> John Abraham >>> [email protected] <mailto:[email protected]> >>> 403-232-1060 >> >> >> -- >> Adrian Klaver >> [email protected] > > > — > John Abraham > -- Adrian Klaver [email protected] ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2019-07-25 00:48 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2019-07-24 23:28 Rounding numbers from copy expert John Abraham <[email protected]> 2019-07-24 23:33 ` Adrian Klaver <[email protected]> 2019-07-25 00:35 ` John Abraham <[email protected]> 2019-07-25 00:48 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox