2

I'm running a query using PostgreSQL 9.6.6 and when querying:

SELECT *
FROM table
WHERE
      threshold_value < VALUE

returns all values = VALUE and not < VALUE as it should. In the same way if I query

SELECT *
FROM table
WHERE
      threshold_value = VALUE

Shows no values at all when all threshold_value (values) are equal to VALUE (VALUE is 1.4)

My threshold_value column is of type real. Should this be happening, or is it a known bug? Is this related to conversion errors between the python implementation of PGAdmin and the DB, or am I missing something?

Improve this question
3
  • 5
    Welcome to the floating point arithmetic world ;) Please read: What Every Programmer Should Know About Floating-Point Arithmetic – ypercubeᵀᴹ Mar 27 '18 at 10:38
  • Well... it's a conversion error. Not an error but rather an interpretation difference. Then, if i want to correctly run the query, should i make a CAST from real to number? How can i not "pitfall" into something like this in more complex situations? – Diogo Guerra Mar 27 '18 at 17:00
  • You could use numeric (in Postgres) and Decimal (in Python) if you want accuracy (to a specific or arbitrary hight accuracy). Or use float/real but never test for equality. Only < and <= and beware that some values, like 1.4 will never be represented exactly as you send them (to Python or Postgres) and make your applications aware. – ypercubeᵀᴹ Mar 27 '18 at 18:45
0

The fractional part is complex

0 * 0.5
1 * 0.25
1 * 0.125
0 * 0.0625
0 * 0.03125
1 * 0.015625
1 * 0.0078125
0 * 0.00390625
0 * 0.001953125
1 * 0.0009765625
1 * 0.00048828125
0 * 0.000244140625

I'm stopping but you can keep going...

0
1
1
0
0
1
1
0
0
1
1

Now we're going to take all the ones and add them up..

1 * 0.25
1 * 0.125
1 * 0.015625
1 * 0.0078125
1 * 0.0009765625
1 * 0.00048828125

The result is 0.39990234375. And, that's the problem here... You can't really write 0.4 in binary. That makes it a floating-point error. That means that the system has a rounding error, and it will try to accommodate for it.

   1/3 = 0.3333333333333333
   1/3 = 0.3333333333333333
+  1/3 = 0.3333333333333333
===========================
   3/3 = 1

Without which the above wouldn't be possible.

Improve this answer
0

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.