Comparing REAL values in PostgreSQL

07 February 2007   0 comments   Linux

Mind That Age!

This blog post is 10 years old! Most likely, its content is outdated. Especially if it's technical.

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)
Follow @peterbe on Twitter

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 by Keyword:
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
Related by Text:
To sub-select or not sub-select in PostgreSQL 31 August 2009
UPPER vs. ILIKE 19 April 2010
Interesting float/int casting in Python 25 April 2006
Sorting transform function in PostgreSQL 03 August 2006
pg_class to check if table exists 20 April 2005