3

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?

Improve this question
5
  • '2' in '25' is greater than '1' in '100'. Hence '25' > '100' Commented Oct 13, 2015 at 0:13
  • Is there anyway to cast this as a double, new to sql, and my db has 200+ columns. Really dont want to redo all of that. Commented Oct 13, 2015 at 0:16
  • Of course there is a way to do that. Commented Oct 13, 2015 at 0:17
  • A question like this is needs a table definition showing exact data types. Commented Oct 13, 2015 at 3:06
  • Side Note : ALTER TABLE "MockTable" ALTER COLUMN "HOURS" TYPE integer USING (trim("HOURS")::integer);(this will convert your text datatype HOURS column to INTEGER ) this will be useful for you Commented Oct 13, 2015 at 4:50

2 Answers 2

Reset to default
6

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Are you sure there will be an implicit cast?
Oh! yea that's definitely an assumption. I'm not as familiar with Postgres as I ought to be. I'll check real quick.
I can save your time: it will be an error: ERROR: operator does not exist: text > integer
Thank you thank you. First time I've had to do SQL so setting it up honestly I really didn't pay attention to the data. But the data has already been prescanned before it is given to me to insure all values contain no errors. Again, THANK YOU! Really didn't want to go back and recreate all the columns lol Note: Implicit doesn't work, but Ex does
Agreed there! MySQL drives me crazy with that kind of crap. "Aggregating with a sum, but you forgot a GROUP BY? No problem... I'll just guess at what you meant... just don't run this query twice and compare the results. Lets be friends"
|
0

If your column names are not all-lowercase, you must specify them in double quotes and with the exact same capitalization as they are in the table:

SELECT * FROM "MockTable" WHERE "Hours" > 25;

Note this says "Hours" instead of "HOURS".

For this reason, it’s best to use all-lowercase for your column and table names. In addition, when using lowercase you won’t need to put the double-quotes around the column names unless they are a reserved word that might mean something else in that context.

Improve this answer

2 Comments

Are you sure it will be an implicit cast?
No, it won’t. @JNevill is right about the need to cast or just fix your table. AND you also need to care about case-sensitivity.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.