Typecast string to integer
PostgresqlPostgresql Problem Overview
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.
Postgresql Solutions
Solution 1 - Postgresql
Wild guess: If your value is an empty string, you can use NULLIF to replace it for a NULL:
SELECT
NULLIF(your_value, '')::int
Solution 2 - Postgresql
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;
Solution 3 - Postgresql
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.
Solution 4 - Postgresql
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
Solution 5 - Postgresql
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
Solution 6 - Postgresql
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
Solution 7 - Postgresql
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
Solution 8 - Postgresql
you can use this query
SUM(NULLIF(conversion_units, '')::numeric)
Solution 9 - Postgresql
And if your column has decimal points
select NULLIF('105.0', '')::decimal
Solution 10 - Postgresql
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