Postgresql – How to convert from Double Precision to Bigint with PostgreSQL

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

Why not simply do the following?

SELECT trunc(num, 2) 
 FROM (VALUES (         9.999999                  ), 
              (         9.999999999999999999999999), 
              (9999999999.999999999999999999999999)
      ) t(num);

/*
     trunc     
───────────────
          9.99
          9.99
 9999999999.99
*/

This way you just truncate it, without assuming anything about the digits.

Notes:

• it looks like you have numbers stored as text. Avoid this, if you can. If this is not the case, just don't explicitly cast anything. If it is, then:
• It might not be obvious, but my example is using numeric - this is the type the parser interprets 9.99999, for example:

SELECT pg_typeof(9.99999);
 pg_typeof 
───────────
 numeric

That is, use an unlimited numeric in your example. This would accomodate all your digits.

• According to the documentation:

  The types decimal and numeric are equivalent.