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

Question

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?

Tobin

Answer 1

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;

Greaser

I would strongly recommend to go with Matthew's suggestion. This solution has issues with strings that look like numbers but are bigger than the maximum value you can place in an integer. – Dumbarton

i second pilif's comment. that max value is a bug waiting to happen. the point of not throwing an error is to not throw an error when the data is invalid. this accepted answer does NOT solve that. thanks Matthew! great work! – Festival

As great as Matthew's answer is, I just needed a quick and dirty way of handling for checking some data. I also admit that my own knowledge is lacking right now in defining functions in SQL. I was only interested in numbers between 1 and 5 digits, so I changed the regex to E'\\d{1,5}$'. – Bemba

Yes, yes this solution is relatively quick and dirty, but in my case I knew what data I had and that the table was relatively short. It's a lot easier than writing (and debugging) an entire function. @Bobort's {1,5} limit above on the digits is possibly a good idea if you're concerned about overflow, but it'll mask larger numbers, which might cause trouble if you're converting a table. Personally I'd rather have the query error out up front and know that some of my "integers" are screwy (You can also select with E'\\d{6,}$' first to make sure). – Greaser

@Anthony Briggs: This will not work if myfield contains " ' " or "," or ".", or '-' – Anglaangle

@StefanSteiger I don't see how that's a problem. How are fields like that getting past the regexp? – Greaser

For a quick check this is really ingenious! Thanks. – Frightened

Answer 2

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)

Ditheism

as opposed to the accepted answer, this solution here is more correct as it can equally well deal with numbers too big to fit into an integer and it is also likely to be faster as it does no validation work in the common case (=valid strings) – Dumbarton

How would you cast string into integer on specific fields using your function while in in INSERT statement? – Commune

Answer 3

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)

Rieth

Solution works only if the input is an integer or NULL. Question was asking to convert any kind of input, and use 0 if its not convertable. – Cornett

@ChrisCogdon I have added to the solution to address your concern with not always giving zero if the value to convert is "not convertable." This tweaked version of the solution will return 0 when a string with no digit characters is given as the value to convert. – Rieth

value "28125400000000003" is out of range for type integer because your regex strips decimal points. Fixing that produces ERROR: invalid input syntax for type integer: "28.125400000000003". I'm after numeric, so wfm :) thanks – Ermentrude

Answer 4

(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 behavior 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 selecting only values::integer will result in an error message.

Sweeny

9 years later, a variation on this was perfect for my similar case--I have NULL and spaces, as well as negative as positive numbers. As given, this solution fails for negative numbers -- but (trim(values) || '.0')::numeric works perfectly. – Crinkle

Time is passing quickly :) – Sweeny

Answer 5

@Matthew's answer is good. But it can be simpler and faster. Also, 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
  LANGUAGE plpgsql PARALLEL SAFE AS
$func$
BEGIN
   IF $1 = '' THEN  -- special case for empty string IF that is common
      RETURN 0;
   ELSIF $1 !~ '^[+-]*\d+$' THEN  -- obviously invalid (would also catch '')
      RETURN 0;  -- or null ?
   ELSE
      RETURN $1::int;
   END IF;

EXCEPTION WHEN OTHERS THEN
   RETURN 0;  -- or null ?  -- other invalid input, like "out of range"

END
$func$;

This returns 0 for an empty string or any other invalid input. Related:

• Find not-integer values in a string column

Note that the simple regular expression does not catch all violations (like "out of range"). So we still need the EXCEPTION clause.
The function can easily be adapted for any data type conversion.

Entering an exception block is expensive. Regular expressions are also expensive - even if on a much smaller scale. If empty strings are common it makes sense to catch that separately. If other exceptions are even remotely common, it pays to filter invalid strings without raising an exception.

Forcemeat

Answer 6

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.

Barmy

That works for the table as it is now. I'm a bit scared that in the future it might contain non-numeric values. I'd have preferred a try/catch-like solution, but this does the trick. Thanks. – Tobin

Maybe you could use regular expressions postgresql.org/docs/8.4/interactive/functions-matching.html but that could be costly. Also accept the answer if it's the solution :) – Evanthe

Answer 7

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

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

Garretson

You would need to handle the null case. So SELECT COALESCE(CAST(SUBSTRING('X12312333333333', '([\d]{1,9})') AS integer), 0); – Henderson

Answer 8

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

Conterminous

have you performed any performance testing for regex vs string function? Also, how does this handle nulls? Would it return 0 or NULL as expected? Thanks! – Backspin

Answer 9

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;  

Demmer

Answer 10

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;

Haifa

Answer 11

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

Farver

Answer 12

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.

Stromberg

In principle you're right, but in this particular scenario I have to optimize a single slow query in an application. I don't know how the code that handles data input work. I don't want to touch it. So far my rewritten query works, but I'd like it not to break in unforeseen cases. Re-architecting the application is not an option, even if it seems the most sensible thing. – Tobin

Answer 13

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.

Quintin

Answer 14

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

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

Featherbedding