silviot silviot - 7 months ago 45
SQL Question

How do I cast a string to integer and have 0 in case of error in the cast with PostgreSQL?

In postgres I have a table with a varchar column. The data is supposed to be integers and I need it in iteger type in a query. Some values are empty strings.
The following:

SELECT myfield::integer FROM mytable


yields
ERROR: invalid input syntax for integer: ""


How can I query a cast and have 0 in case of error during the cast in postgres?

Answer

I was just wrestling with a similar problem myself, but didn't want the overhead of a function. I came up with the following query:

SELECT myfield::integer FROM mytable WHERE myfield ~ E'^\\d+$';

Postgres shortcuts its conditionals, so you shouldn't get any non-integers hitting your ::integer cast. It also handles NULL values (they won't match the regexp).

If you want zeros instead of not selecting, then a CASE statement should work:

SELECT CASE WHEN myfield~E'^\\d+$' THEN myfield::integer ELSE 0 END FROM mytable;