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

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?

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

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

I found the following code easy and working. Original answer is here https://www.postgresql.org/message-id/[email protected]

prova=> create table test(t text, i integer);
CREATE

prova=> insert into test values('123',123);
INSERT 64579 1

prova=> select cast(i as text),cast(t as int)from test;
text|int4
----+----
123| 123
(1 row)

hope it helps

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

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

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;

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.

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.

@Matthew's answer is good. But it can be simpler and faster. And the question asks to convert empty strings ('') to 0, but not other "invalid input syntax" or "out of range" input:

CREATE OR REPLACE FUNCTION convert_to_int(text)
  RETURNS int AS
$func$
BEGIN
   IF $1 = '' THEN  -- special case for empty string like requested
      RETURN 0;
   ELSE
      RETURN $1::int;
   END IF;

EXCEPTION WHEN OTHERS THEN
   RETURN NULL;  -- NULL for other invalid input

END
$func$  LANGUAGE plpgsql IMMUTABLE;

This returns 0 for an empty string and NULL for any other invalid input.
It can easily be adapted for any data type conversion.

Entering an exception block is substantially more expensive. If empty strings are common it makes sense to catch that case before raising an exception.
If empty strings are very rare, it pays to move the test to the exception clause.

CREATE OR REPLACE FUNCTION parse_int(s TEXT) RETURNS INT AS $$
BEGIN
  RETURN regexp_replace(('0' || s), '[^\d]', '', 'g')::INT;
END;
$$ LANGUAGE plpgsql;

This function will always return 0 if there are no digits in the input string.

SELECT parse_int('test12_3test');

will return 123

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.

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;  

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)

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.