# Comparing REAL values in PostgreSQL

07 February 2007   0 comments   Linux

Mind That Age!

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

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

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