Comparing REAL values in PostgreSQL

07 February 2007   0 comments   Linux

Powered by Fusion×

Long story short, if you need to compare floating point numbers against columns defined as REAL you need to first cast them to NUMERIC in PostgreSQL. And to compare equality between two numbers with different amount of significant figures you have to use ROUND().

Less or greater is obvious to work with because it doesn't require the same precision:

mobilexpenses=# select vat from expenses where vat >= 18.98 and vat <= 20.0 ;
  vat   
---------
 19.6596
(1 row)

So there's a record there with vat ~= 19.66, doing an exact select on that won't work:

mobilexpenses=# select vat from expenses where vat = 19.6596;
 vat 
-----
(0 rows)

Why?? Well, you should never compare floating point numbers for equality. So what's the solution then? Solution: cast it to numeric and use round() depending on your comparison nature:

 mobilexpenses=# select vat from expenses where vat::numeric = 19.6596;
  vat   
---------
 19.6596
(1 row)

And if you want to compare it with 19.66?:

mobilexpenses=# select vat from expenses where vat::numeric = 19.66;
 vat 
-----
(0 rows)

Time to take out the ROUND() function:

mobilexpenses=# select vat from expenses where round(vat::numeric, 2) = 19.66;
  vat   
---------
 19.6596
(1 row)

Comments

Thank you for posting a comment

Your email will never ever be published


Related posts

Previous:
A Flash interface that doesn't suck 04 February 2007
Next:
Vista voice recognition and Perl 09 February 2007
Related:
Formatting numeric amounts in Javascript 16 January 2009
Calculator in Python for dummies 17 December 2007
Interesting float/int casting in Python 25 April 2006