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

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)

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.

SUBSTRING may help for some cases, you can limit the size of the int.

SELECT CAST(SUBSTRING('X12312333333333', '([\d]{1,9})') AS integer);

Finally I manage to ignore the invalid characters and get only the numbers to convert the text to numeric.

SELECT (NULLIF(regexp_replace(split_part(column1, '.', 1), '\D','','g'), '') 
    || '.' || COALESCE(NULLIF(regexp_replace(split_part(column1, '.', 2), '\D','','g'),''),'00')) AS result,column1
FROM (VALUES
    ('ggg'),('3,0 kg'),('15 kg.'),('2x3,25'),('96+109'),('1.10'),('132123')
) strings;  

The following function does

• use a default value (error_result) for not castable results e.g abc or 999999999999999999999999999999999999999999
• keeps null as null
• trims away spaces and other whitespace in input
• values casted as valid bigints are compared against lower_bound to e.g enforce positive values only

CREATE OR REPLACE FUNCTION cast_to_bigint(text) 
RETURNS BIGINT AS $$
DECLARE big_int_value BIGINT DEFAULT NULL;
DECLARE error_result  BIGINT DEFAULT -1;
DECLARE lower_bound   BIGINT DEFAULT 0;
BEGIN
    BEGIN
        big_int_value := CASE WHEN $1 IS NOT NULL THEN GREATEST(TRIM($1)::BIGINT, lower_bound) END;
    EXCEPTION WHEN OTHERS THEN
        big_int_value := error_result;
    END;
RETURN big_int_value;
END;

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.

I also have the same need but that works with JPA 2.0 and Hibernate 5.0.2:

SELECT p FROM MatchProfile p WHERE CONCAT(p.id, '') = :keyword

Works wonders. I think it works with LIKE too.

This should also do the job but this is across SQL and not postgres specific.

select avg(cast(mynumber as numeric)) from my table