Search code examples
postgresqlcomparison-operators

How should I query an integer where there are decimals in the data?

SELECT * FROM table1 WHERE spent>= '1000'

This query still bring out numbers such as 598.99 and 230.909. My question is why is it doing this when I asked to search over or equal to 1000. Is there anyway to query so it only shows equal and more than 1000?

Solution

  • This happens because your '1000' is a text value. The other value is (or is converted to) text, too, so you end up with byte-per-byte comparison.

    598.99 is greater then 1000 because 5... is greater then 1....

    Cast to numeric types to do a proper comparison:

    SELECT * FROM table1 WHERE spent::numeric >= '1000'::numeric

    Or simply:

    SELECT * FROM table1 WHERE spent::numeric >= 1000