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

Question

In PostgreSQL, I have a table with a varchar column. The data is supposed to be integers and I need it in integer 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 1

Use the query as follows:

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

In Postgres, you can't get any non-integers hitting your:: integer cast. It is also used to handles NULL values (but, they won't match the regexp). But, if you want zeros instead of not selecting, then use the CASE statement like this:

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