Do you have old friends you don't want to lose contact with?

Home Plog Comparing REAL values in PostgreSQL

  Mobile version of this page


 

Comparing REAL values in PostgreSQL

equality, significant figures, floating point, floating, round, numeric, cast

7^th of February 2007

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)

Comment