POSTGRESQL Comparison Operators on text

Question

I am trying to do comparison operators on my postgresql data.

I have a range of data like this

MockTable

ID | IDName | Hours | Minutes |     
43   John     30      100    
37   Jane     20      92    
1    Don      100     22    

Each of the fields on the top are labeled in the SQL server as text. I am trying to pull information like this.

Select *
From "MockTable"
WHERE "HOURS">'25';    

Thus recieving the 1st and 3rd column back. However I am not getting this results. Input?

Answer 1

When you compare numbers as strings, you have to think about alphabetical order. The magnitude of the number is meaningless, at this point is merely a word. So "25" is greater than "100" because the "2" comes after the "1" in an alphabetical sense.

What you need to do is either cast your "Hours" field as integer or fix the table so you aren't storing numbers in a string typed column.

Select * From "MockTable" WHERE CAST("Hours" as INTEGER) > 25;

Obviously, you are going to run into some difficult problems if there are records where the "Hours" field contains non-numeric characters, so you'll have to deal with that if and when it arises.