I am importing data from a table which has raw feeds in Varchar, I need to import a column in varchar into a string column. I tried using the <column_name>::integer as well as to_number(<column_name>,'9999999') but I am getting errors, as there are a few empty fields, I need to retrieve them as empty or null into the new table.
11 Answers
Wild guess: If your value is an empty string, you can use NULLIF to replace it for a NULL:
SELECT
NULLIF(your_value, '')::int
Comments
You can even go one further and restrict on this coalesced field such as, for example:-
SELECT CAST(coalesce(<column>, '0') AS integer) as new_field
from <table>
where CAST(coalesce(<column>, '0') AS integer) >= 10;
2 Comments
Jeremy Meek
why does coalesce need casting?
Nic Nilov
Coalesce itself does not need casting. It only ensures '0' (a string) is returned instead of
NULL. It is the resulting value (which is a string) that needs casting to integer, per the original question.If you need to treat empty columns as NULLs, try this:
SELECT CAST(nullif(<column>, '') AS integer);
On the other hand, if you do have NULL values that you need to avoid, try:
SELECT CAST(coalesce(<column>, '0') AS integer);
I do agree, error message would help a lot.
Comments
The only way I succeed to not having an error because of NULL, or special characters or empty string is by doing this:
SELECT REGEXP_REPLACE(COALESCE(<column>::character varying, '0'), '[^0-9]*' ,'0')::integer FROM table
4 Comments
Jasper de Vries
For me (9.6.2) this was the only thing that worked, all the other answers failed.
Matthieu
Couldn't you add a
WHERE <column> != NULL?
Jade Hamel
Mathieu: adding WHERE column will exclude lines with NULL values in that column and maybe you want the NULL column to be converted to 0
Jade Hamel
Mathieu: COALESCE() will convert NULL to 0
I'm not able to comment (too little reputation? I'm pretty new) on Lukas' post.
On my PG setup to_number(NULL) does not work, so my solution would be:
SELECT CASE WHEN column = NULL THEN NULL ELSE column :: Integer END
FROM table
1 Comment
kgrittn
This should work, but it should be an exact equivalent of the less verbose
NULLIF() approach. The standard actually defines NULLIF as a form of the CASE predicate.If the value contains non-numeric characters, you can convert the value to an integer as follows:
SELECT CASE WHEN <column>~E'^\\d+$' THEN CAST (<column> AS INTEGER) ELSE 0 END FROM table;
The CASE operator checks the < column>, if it matches the integer pattern, it converts the rate into an integer, otherwise it returns 0
Comments
Common issue
Naively type casting any string into an integer like so
SELECT ''::integer
Often results to the famous error:
Query failed: ERROR: invalid input syntax for integer: ""
Problem
PostgreSQL has no pre-defined function for safely type casting any string into an integer.
Solution
Create a user-defined function inspired by PHP's intval() function.
CREATE FUNCTION intval(character varying) RETURNS integer AS $$
SELECT
CASE
WHEN length(btrim(regexp_replace($1, '[^0-9]', '','g')))>0 THEN btrim(regexp_replace($1, '[^0-9]', '','g'))::integer
ELSE 0
END AS intval;
$$
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
Usage
/* Example 1 */
SELECT intval('9000');
-- output: 9000
/* Example 2 */
SELECT intval('9gag');
-- output: 9
/* Example 3 */
SELECT intval('the quick brown fox jumps over the lazy dog');
-- output: 0
2 Comments
jaisonDavis
Would fail for very large numbers. It'd work perfectly if you catch exceptions as well
Abel Callejo
@jaisonDavis the topic is about integer that's why it is expected that this solution would fail for very large numbers. You might want to consider creating a similar function to this solution eg:
bigintval() and for that function replace anything that says integer and replace it with bigint. The fail is probably just about the data type limits.you can use this query
SUM(NULLIF(conversion_units, '')::numeric)
Comments
The perfect solution for me is to use nullif and regexp_replace
SELECT NULLIF(REGEXP_REPLACE('98123162t3712t37', '[^0-9]', '', 'g'), '')::bigint;
Above solution consider the following edge cases.
- String and Number: only the
regexp_replacefunction perfectly converts into integers.
SELECT NULLIF(REGEXP_REPLACE('string and 12345', '[^0-9]', '', 'g'), '')::bigint;
- Only string:
regexp_replaceconverts non-string characters to empty strings; which can't cast directly to integer so usenullifto convert to null
SELECT NULLIF(REGEXP_REPLACE('only string', '[^0-9]', '', 'g'), '')::bigint;
- Integer range: Converting a string into integer may cause out of range for type integer error. So use
bigintinstead
SELECT NULLIF(REGEXP_REPLACE('98123162t3712t37', '[^0-9]', '', 'g'), '')::bigint;
Comments
And if your column has decimal points
select NULLIF('105.0', '')::decimal
Comments
This works for me:
select (left(regexp_replace(coalesce('<column_name>', '0') || '', '[^0-9]', '', 'g'), 8) || '0')::integer
For easy view:
select (
left(
regexp_replace(
-- if null then '0', and convert to string for regexp
coalesce('<column_name>', '0') || '',
'[^0-9]',
'',
'g'
), -- remove everything except numbers
8 -- ensure ::integer doesn't overload
) || '0' -- ensure not empty string gets to ::integer
)::integer
Query failed: ERROR: invalid input syntax for integer: ""see the solution using the intval() function