postgresqlsqldatatypesalter-column

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

Solution

  • 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);