CODEWITHSUNDEEP
sqlpostgresql
khajlk
khajlk

Reputation: 861

Error: Unable to replace text string in PostgreSQL

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.

Upvotes: 0

Views: 180

Answers (1)

klin
klin

Reputation: 121634

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;

Upvotes: 2

Related Questions