1

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?
8

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

|improve this answer|||||
  • postgresql.org/docs/9.3/static/… also starts with a warning to this effect, basically. – dezso Sep 12 '14 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 :-) – user1363989 Sep 12 '14 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. – David Spillett Sep 12 '14 at 13:49
  • 2
    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. – Erwin Brandstetter Sep 12 '14 at 16:23
  • 1
    Useful answer, but hampered by MS-SQL-ism in the second part. I'm sick of multi-part list-of-questions, so +1 anyway. – Craig Ringer Sep 12 '14 at 16:57
3

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}';
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.