# sum of double precision gives weird results

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?
• html version, in Oracle's website Sep 12, 2014 at 9:54
• You should probably use a fixed-point datatype, instead of double (since you say that all your numbers have 2 digits accuracy after the decimal point.) Like `NUMERIC(16,2)` or just `NUMERIC`. See Postgres number dadatypes Sep 12, 2014 at 10:00
• As soon as you find yourself writing "my questions are" that's a hint that you might want to consider posting two questions and linking between. Sep 12, 2014 at 16:57

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).

• postgresql.org/docs/9.3/static/… also starts with a warning to this effect, basically. Sep 12, 2014 at 10:26
• side note: I've encountered the same problem calculating with dates in oracle. it was like: (date_b - date_a ) / 5min. This really sucked :-) Sep 12, 2014 at 10:44
• @user1363989: Date+Time values tend to be stored in integer form (either a single seconds or milliseconds since a certain epoc, or as two values on being the the number of days since the epoc and the other being the number of (milli)seconds since 00:00:00.000 - but once you start doing direct arithmetic they are often coerced into floats by default. You are better of either using built-in functions like DATEDIFF() where available as much as possible, or explicitly casting the DATETIME value to an appropriate decimal/numeric type before performing any arithmetic (+, -, *, /, ...) on them. Sep 12, 2014 at 13:49
• Modern-day Postgres uses 8-byte integers for `timestamp` and 4-byte integer for `date`. Calculations are exact. There is no `DATEDIFF()` in Postgres and no casting is necessary. Sep 12, 2014 at 16:23
• Useful answer, but hampered by MS-SQL-ism in the second part. I'm sick of multi-part list-of-questions, so +1 anyway. Sep 12, 2014 at 16:57

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}';
``````

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;
``````