How to cast String into int in PostgreSql

Question

I want to cast string into Integer. I have a table like this.

Have:

ID     Salary
1      "$1,000"
2      "$2,000"

Want:

ID   Salary
1    1000
2    2000

My query

Select Id, cast(substring(Salary,2, length(salary)) as int) 
from have

I am getting error.

ERROR: invalid input syntax for type integer: "1,000"
SQL state: 22P02

Can anyone please provide some guidance on this.

You can use the REGEXP_REPLACE function SELECT REGEXP_REPLACE(salary, ',|\$', '', 'g')::INT FROM test; — Oluwafemi Sule
– Oluwafemi Sule, Commented Sep 17, 2021 at 6:46
There is also to_number function if you know the maximum number of digits your current could have SELECT to_number(salary, 'L99999999999') FROM test; — Oluwafemi Sule
– Oluwafemi Sule, Commented Sep 17, 2021 at 7:01

score 2 · Accepted Answer · 2021-09-17 09:37:46Z

2

Remove all non-digit characters, then you cast it to an integer:

regexp_replace(salary, '[^0-9]+', '', 'g')::int

But instead of trying to convert the value every time you select it, fix your database design and convert the column to a proper integer. Never store numbers in text columns.

alter table bad_design 
     alter salary type int using regexp_replace(salary, '[^0-9]+', '', 'g')::int;

edited Sep 17, 2021 at 9:37

answered Sep 17, 2021 at 8:14

user330315

Sign up to request clarification or add additional context in comments.

5 Comments

Dharmendra Kumar Yadav Over a year ago

Thanks for your input, but I really can not change the source table, I have to use that column and get it stored into my working table with no comma and converted to number.

user330315 Over a year ago

If you really can't fix the broken design, then you will have to use the regexp_replace() expression in a SELECT query

Dharmendra Kumar Yadav Over a year ago

select id,salary, regexp_replace(salary, '[^0-9]+', 'g')::int new_salary from have ran this but getting below error ERROR: invalid input syntax for type integer: "g1,000"

user330315 Over a year ago

@DharmendraKumarYadav: sorry a typo in the first expression (the second one was correct)

Miral Over a year ago

Be wary of salaries with decimal places or different currencies (or commas as decimal separator) when doing this sort of thing...

Collectives™ on Stack Overflow

How to cast String into int in PostgreSql

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related