I would like to return all rows where the sv column, a varchar, is greater than 40. How can I convert sv to an integer on the fly. The line below returns ERROR: invalid input syntax for integer: "SV"
SELECT namefirst, namelast, yearid FROM pitching JOIN people ON pitching.playerID = people.playerID WHERE CAST(sv AS INTEGER)>40;
Postgres doesn't have a built-in way to avoid conversion errors. One method is to use a case expression:
WHERE (CASE WHEN sv ~ '^[0-9]+$' THEN sv::integer END) > 40
Or, if the integers are zero padded on the left, then you might be able to use string comparisons:
WHERE sv >= '40'
However, this runs the risk of matching non-numeric values (which you seem to have given the error you are getting).
