CODEWITHSUNDEEP
postgresqlsqldatatypesalter-column
Alex
Alex

Reputation: 35

Alter Column TYPE USING - cannot change from varchar to numeric

I would be eternaly grateful if somebody could help me a bit. I am totally new to Postrgresql 10 I had a large file millions of lines 73 columns, I could not imported so I set all the columns to varchar. Now I need to manipulate the data I cannot change the datatype. I tried for hours. The column contains a few values with 1 or 2 decimals. This is what I am doing:

ALTER TABLE table1 
ALTER COLUMN facevalue TYPE numeric USING (facevalue::numeric);

this is the error I get

ERROR:  invalid input syntax for type numeric: " "
SQL state: 22P02

Thank you for your time and consideration

Upvotes: 0

Views: 918

Answers (1)

user330315
user330315

Reputation:

you apparently have empty strings or whitespace only values. You need to convert them to NULL

ALTER TABLE table1 
   ALTER COLUMN facevalue TYPE numeric USING (nullif(trim(facevalue),'')::numeric);

Upvotes: 1

Related Questions