sum of double precision gives weird results

Question

I've been making a sum of a column containing about 500.000 records of double precision numbers. All numbers in the database should normally be of two ciphers behind the period. However when making the sum I get 6 numbers after the period: 123123123.549977

Either I have bad data in my database where there are records with more numbers after the period or there is something I'm missing about the sum function.

So my questions are:

• Does the sum function have any rounding properties which may cause this?
• Is there a way to select all records containing more than two numbers after the period?

Answer 1

This is a common problem with floating point numbers everywhere.

Floating point numbers stored in computer systems should only ever be considered approximations because there are numbers easy to represent in decimal that come out longer than the available precision (sometimes they are in fact never ending) when converted to binary. See ypercube's links and https://stackoverflow.com/questions/588004/is-floating-point-math-broken amongst many other references, for more detail.

The most common example given (as seen in that StackOverflow link) is 0.1+0.2 not resulting in exactly 0.3. You have to apply extra rounding or flexible bounds checking (in both cases reducing the effective precision) to get the behaviour you are expecting.

As your data has a fixed number of decimal places (or a fixed maximum) at 2, you would be much better of using the fixed place decimal/numeric types or similar. These are actually stored and processed as scaled integers avoiding the need for any floating point representation internally so avoiding the approximation problem (all integers can be represented accurately in both base 10 and base 2, assuming you have enough digits/bits). For instance 0.1 and 0.2 might be stored as 1000 and 2000, so the addition results in 3000 which when converted to a string for display becomes 0.3 not the 0.3000000004 you might get out of a floating point calculation, and of course compares accurately to 3 in the same type which would be scaled to 3000.

Is there a way to select all records containing more than two numbers after the period?

You could convert to string and count the characters after the decimal point (or comma depending on your system local). In TSQL this would find values that are have ended up with more than two decimal places after being stored in a binary float format then translated back to decimal:

SELECT *
FROM   myTable
WHERE  CHARINDEX('.', REVERSE(CAST(floatValue AS NVARCHAR(MAX)))>3

(you may need to tweak function names and related syntax for postgres).

Answer 2

David's answer is correct except for the code, which is nonsensical in PostgreSQL.

You're looking for > 2 decimal places. The easiest way to find that is to cast to NUMERIC (arbitrary-precision decimal floating point) and subtract the rounded value from the original.

SELECT *
FROM mytable
WHERE (CAST(floatvalue AS numeric) - round(CAST(floatvalue AS numeric), 2)) <> 0;

(That's actually pretty much standard SQL, it should work on many DBs with adjustments for the data type - DECIMAL, NUMBER, DECFLOAT, NUMERIC, etc).

Another option is to set extra_float_digits=1 to deal with error from float/dec imprecision on float-to-string formatting and then do a simple pattern match:

SELECT *
FROM mytable
WHERE floatvalue::text ~ '\.\d{3}';

Answer 3

An alternate solution I have tried is to convert to varchar and then convert back to DOUBLE precision as in the following example:

SELECT SUM(CAST( CAST(doubleprecisionvalue AS VARCHAR(50)) AS DOUBLE PRECISION))TOTAL
FROM MYTABLE;