0 votes
1 view
in SQL by (20.3k points)

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


ERROR:  invalid input syntax for integer: ""

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

1 Answer

0 votes
by (40.4k points)

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;

Related questions

Welcome to Intellipaat Community. Get your technical queries answered by top developers !