Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2012

    Unanswered: to_number function

    Hello people,
    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?

  2. #2
    Join Date
    Nov 2003
    Provided Answers: 8
    This is hidden in the documentation for the ALTER TABLE statement:

    Quote Originally Posted by The Manual
    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')
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags:

    Tips for good questions:

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts