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?
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)
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)
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.
@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.
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.
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
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;
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.
The following function does
error_result) for not castable results e.g abc or 999999999999999999999999999999999999999999null as nullbigints are compared against lower_bound to e.g enforce positive values onlyCREATE 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;
