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

Question

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 1

SELECT CASE WHEN myfield="" THEN 0 ELSE myfield::integer END FROM mytable

I haven't ever worked with PostgreSQL but I checked the manual for the correct syntax of IF statements in SELECT queries.

Answer 2

If the data is supposed to be integers, and you only need those values as integers, why don't you go the whole mile and convert the column into an integer column?

Then you could do this conversion of illegal values into zeroes just once, at the point of the system where the data is inserted into the table.

With the above conversion you are forcing Postgres to convert those values again and again for each single row in each query for that table - this can seriously degrade performance if you do a lot of queries against this column in this table.

Answer 3

You could also create your own conversion function, inside which you can use exception blocks:

CREATE OR REPLACE FUNCTION convert_to_integer(v_input text)
RETURNS INTEGER AS $$
DECLARE v_int_value INTEGER DEFAULT NULL;
BEGIN
    BEGIN
        v_int_value := v_input::INTEGER;
    EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'Invalid integer value: "%".  Returning NULL.', v_input;
        RETURN NULL;
    END;
RETURN v_int_value;
END;
$$ LANGUAGE plpgsql;

Testing:

=# select convert_to_integer('1234');
 convert_to_integer 
--------------------
               1234
(1 row)

=# select convert_to_integer('');
NOTICE:  Invalid integer value: "".  Returning NULL.
 convert_to_integer 
--------------------

(1 row)

=# select convert_to_integer('chicken');
NOTICE:  Invalid integer value: "chicken".  Returning NULL.
 convert_to_integer 
--------------------

(1 row)

Answer 4

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;