Typecast string to integer

Question

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.

If the error is something like `Query failed: ERROR: invalid input syntax for integer: ""` see the solution using the [intval()](https://stackoverflow.com/questions/10518258/typecast-string-to-integer-postgres#60485123) function — Abel Callejo, Oct 17 '20 at 00:29

score 178 · Answer 1 · answered May 09 '12 at 14:43

178

Wild guess: If your value is an empty string, you can use NULLIF to replace it for a NULL:

SELECT
    NULLIF(your_value, '')::int

answered May 09 '12 at 14:43

Frank Heikens

117,544
24
142
135

score 70 · Answer 2 · answered Dec 01 '12 at 13:38

70

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;

answered Dec 01 '12 at 13:38

tatty

766
6
4

1

why does coalesce need casting? – Jeremy Meek Mar 12 '22 at 21:40
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. – Nic Nilov Nov 19 '22 at 16:28

vyegorov · Answer 3 · 2012-05-09T16:19:25.703

35

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.

edited May 09 '12 at 16:19

answered May 09 '12 at 14:44

vyegorov

21,787
7
59
73

score 30 · Answer 4 · edited Mar 02 '20 at 07:10

30

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

edited Mar 02 '20 at 07:10

Abel Callejo

13,779
10
69
84

answered Jun 01 '15 at 20:22

Jade Hamel

1,360
1
15
30

2

For me (9.6.2) this was the only thing that worked, all the other answers failed. – Jasper de Vries Jul 14 '17 at 13:20
2

Couldn't you add a `WHERE != NULL`? – Matthieu Mar 12 '18 at 17:03
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 Jul 16 '23 at 14:29

score 16 · Answer 5 · edited Jul 25 '14 at 17:12

16

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

edited Jul 25 '14 at 17:12

Mike Guthrie

4,029
2
25
48

answered May 09 '12 at 14:46

niko

1,816
13
13

1

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. – kgrittn May 09 '12 at 16:13

score 15 · Answer 6 · answered Jun 22 '18 at 08:59

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

Abel Callejo · Answer 7 · 2020-03-18T08:17:38.977

9

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

edited Mar 18 '20 at 08:17

answered Mar 02 '20 at 08:40

Abel Callejo

13,779
10
69
84

Would fail for very large numbers. It'd work perfectly if you catch exceptions as well – jaisonDavis Jul 19 '21 at 12:45
@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](https://www.postgresql.org/docs/9.5/datatype-numeric.html) limits. – Abel Callejo Jul 20 '21 at 00:47

score 3 · Answer 8 · edited Jan 10 '20 at 12:46

3

you can use this query

SUM(NULLIF(conversion_units, '')::numeric)

edited Jan 10 '20 at 12:46

slfan

8,950
115
65
78

answered Jan 10 '20 at 12:21

Abdul Quadir

61
3

score 1 · Answer 9 · answered Feb 01 '23 at 03:45

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_replace function perfectly converts into integers.

SELECT NULLIF(REGEXP_REPLACE('string and 12345', '[^0-9]', '', 'g'), '')::bigint;

Only string: regexp_replace converts non-string characters to empty strings; which can't cast directly to integer so use nullif to 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 bigint instead

SELECT NULLIF(REGEXP_REPLACE('98123162t3712t37', '[^0-9]', '', 'g'), '')::bigint;

score 0 · Answer 10 · answered Apr 14 '21 at 06:53

0

And if your column has decimal points

select NULLIF('105.0', '')::decimal

answered Apr 14 '21 at 06:53

Omari Victor Omosa

2,814
2
24
46

Konstantin Chemshirov · Answer 11 · 2022-02-18T12:46:38.460

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

Typecast string to integer

11 Answers11

Common issue

Problem

Solution

Usage

Linked