Thread

1. Precision errors in float8 type casting (as of 7.3.2 and some earlier releases at least)

   Philip Edelbrock <phil@edgedesign.us> — 2003-07-10T00:16:08Z

   
   I ran into this oddity today and tested it on a few of our PosgreSQL 
   backends (all of which gave the same response):
   
   phil=# select 3.85::float4*1;
        ?column?    
   ------------------
    3.84999990463257
   (1 row)
   
   phil=# select 3.85::float4*1::float8;
        ?column?    
   ------------------
    3.84999990463257
   
   phil=# select (3.85::float4)::float8;
         float8     
   ------------------
    3.84999990463257
   (1 row)
   
   (Or substitute 3.85 for any number with something other than 0 to the 
   right of the decimal point, or pull the same values from any table which 
   stores in float4/real format.)
   
   Obviously, this is wrong and should return 3.85.  We traced this down on 
   an ecom server which was shaving off pennies from some transactions 
   (because we truncate to the hundredths place instead of rounding what we 
   get back from the SQL backend).
   
   The newest server we have is 7.3.2, so I haven't tried this on the 
   current 7.3.3 release.
   
   phil=# select version();
                                                    
   version                                                
   ---------------------------------------------------------------------------------------------------------
    PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 
   20020903 (Red Hat Linux 8.0 3.2-7)
   (1 row)
   
   
   Phil
   
   
   

2. Re: Precision errors in float8 type casting (as of 7.3.2 and

   Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2003-07-10T04:44:02Z

   On Wed, 9 Jul 2003, Philip Edelbrock wrote:
   
   > phil=# select (3.85::float4)::float8;
   >       float8
   > ------------------
   >  3.84999990463257
   > (1 row)
   >
   > (Or substitute 3.85 for any number with something other than 0 to the
   > right of the decimal point, or pull the same values from any table which
   > stores in float4/real format.)
   >
   > Obviously, this is wrong and should return 3.85.  We traced this down on
   
   I see no obviously about it.  Once you've placed a value in a float you
   are accepting the chance of some precision loss.  When we print a float4
   we can print it with an amount of precision that generally limits this
   (although you'll see things like 3.849998 -> 3.5), but once you cast it to
   a float8 those values are distinguishably different.  In theory one could
   keep the history of the value around to determine a precision, but that
   doesn't really seem better in general.