it is my first post here! Thank you all for any suggestion and help.
I'm a newby in PostgreSQL. I'm working on a PostgreSQL table imported from an .xls table. I have a column named "score" where values are stored yet. To be able to import the table from .xls I had to set the TYPE of the PostgreSQL column "score" on TEXT, because the data stored can be expressed in different way (previous values were generated by FileMakerPro): NULL, or a number expressed in different ways like: 3 or 3,5, or 3,50 or 3,75... to 10, which is the maximum number accepted in this column.
Now, I need to convert the TYPE of my column from TEXT to NUMERIC (with the possibility to have decimal number with a maximum of 2 characters after the comma). I surfed the PostgreSQL Guide finding the to_number function, but it is really to difficult for me in this moment to understand the way to use it properly according to the variability of my data in the column "store".
Anyone could help me, please?
SET DATA TYPE
The optional USING clause specifies how to compute the new column value from the old; if omitted, the default conversion is the same as an assignment cast from old data type to new. A USING clause must be provided if there is no implicit or assignment cast from old to new type.
As your values contain commas as the decimal separator, you must replace that before applying the to_number() function because it's quite a hassle to make it handle anything else than a dot properly:
alter table foo
ALTER COLUMN score TYPE numeric(5,2)
using to_number(replace(score,',','.'), '99.99')