CODEWITHSUNDEEP
sqlpostgresql
Andrew Lastrapes
Andrew Lastrapes

Reputation: 187

Converting column value from varchar to integer in PostgreSql

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;

Upvotes: 1

Views: 677

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

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

Upvotes: 2

Related Questions