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

Question

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;

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)

This might be somewhat of a hack, but it got the job done in our case:

(0 || myfield)::integer

Explanation (Tested on Postgres 8.4):

The above mentioned expression yields NULL for NULL-values in myfield and 0 for empty strings (This exact behaviour may or may not fit your use case).

SELECT id, (0 || values)::integer from test_table ORDER BY id

Test data:

CREATE TABLE test_table
(
  id integer NOT NULL,
  description character varying,
  "values" character varying,
  CONSTRAINT id PRIMARY KEY (id)
)

-- Insert Test Data
INSERT INTO test_table VALUES (1, 'null', NULL);
INSERT INTO test_table VALUES (2, 'empty string', '');
INSERT INTO test_table VALUES (3, 'one', '1');

The query will yield the following result:

 ---------------------
 |1|null        |NULL|
 |2|empty string|0   |
 |3|one         |1   |
 ---------------------

Whereas select only values::integer will result in an error message.

Hope this helps.

I had the same sort of need and found this to work well for me (postgres 8.4):

CAST((COALESCE(myfield,'0')) AS INTEGER)

Some test cases to demonstrate:

db=> select CAST((COALESCE(NULL,'0')) AS INTEGER);
 int4
------
    0
(1 row)

db=> select CAST((COALESCE('','0')) AS INTEGER);
 int4
------
    0
(1 row)

db=> select CAST((COALESCE('4','0')) AS INTEGER);
 int4
------
    4
(1 row)

db=> select CAST((COALESCE('bad','0')) AS INTEGER);
ERROR:  invalid input syntax for integer: "bad"

If you need to handle the possibility of the field having non-numeric text (such as "100bad") you can use regexp_replace to strip non-numeric characters before the cast.

CAST(REGEXP_REPLACE(COALESCE(myfield,'0'), '[^0-9]+', '', 'g') AS INTEGER)

Then text/varchar values like "b3ad5" will also give numbers

db=> select CAST(REGEXP_REPLACE(COALESCE('b3ad5','0'), '[^0-9]+', '', 'g') AS INTEGER);
 regexp_replace
----------------
             35
(1 row)

To address Chris Cogdon's concern with the solution not giving 0 for all cases, including a case such as "bad" (no digit characters at all), I made this adjusted statement:

CAST((COALESCE(NULLIF(REGEXP_REPLACE(myfield, '[^0-9]+', '', 'g'), ''), '0')) AS INTEGER);

It works similar to the simpler solutions, except will give 0 when the value to convert is non-digit characters only, such as "bad":

db=> select CAST((COALESCE(NULLIF(REGEXP_REPLACE('no longer bad!', '[^0-9]+', '', 'g'), ''), '0')) AS INTEGER);
     coalesce
----------
        0
(1 row)