Error: Unable to replace text string in PostgreSQL

Question

In my PostgreSQL 9.5 database, I have a table called my_table containing three columns id character varying (254), val_1 numeric and val_2 numeric. The following query:

Select id, val_1, val_2 from my_table limit 4;

returns four sample rows:

id         val_1   val_2
1147_1     72      61
1228_1     73      67
1255_1     74      68
1337_1     79      66

I wanted to change the data type of id column thus I tried:

Alter table my_table alter column id type integer using id::integer;

But I get this error:

ERROR:  invalid input syntax for integer: "1147_1"

To get rid of these "", I tried:

Update my_table set id = replace(id, '"','');

The query returned successfully. But, when I tried to change the data type I again get the same error ERROR: invalid input syntax for integer: "1147_1". Can someone help me to fix this problem? I am unable to figure out that why I keep getting the same error even I successfully executed replace() command.

1147_1 is not a valid integer. How do you want to convert it? Should it be 1147 or 11471? — klin
– klin, Commented Sep 2, 2017 at 23:04
In that case, I think I can extract the number using substring() command. I wanted to change the data type for sorting purposes. — khajlk
– khajlk, Commented Sep 2, 2017 at 23:06

klin · Accepted Answer · 2017-09-02 23:09:56Z

2

You have to change the id values to be a valid integers, e.g. remove underscores:

alter table my_table alter column id type integer using replace(id, '_', '')::integer;

answered Sep 2, 2017 at 23:09

klin

123k15 gold badges240 silver badges262 bronze badges

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

Collectives™ on Stack Overflow

Error: Unable to replace text string in PostgreSQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related