In response to Raymond O'Donnell <r...@iol.ie>: > On 24/02/2010 19:53, Christine Penner wrote: > > > At 11:38 AM 24/02/2010, you wrote: > >> In response to Christine Penner <christ...@ingenioussoftware.com>: > >> > >> > I have a character field I want to change to a number. The values in > >> > that field are all numbers that may or may not be padded with spaces > >> > or 0's. What is the best way to do that? > >> > >> Put the values in numeric fields to begin with and cast to chars as > >> needed. Basically reverse what you're doing. > > > > I don't understand what you mean. This is a column in a table that is > > already a char and has numbers in it. I want it to be a number field not > > character. How can I change the data type of that column without loosing > > the data I have in it? > > I think what he means is that you should have been doing the reverse to > begin with - storing numbers in the database as numeric columns, and > then casting them to a character format as needed for display.
Actually, I misunderstood the question. I thought you were trying to figure out how to extract the data for display. But fixing the fields to be the right type is a noble goal :) > However, to address your immediate problem, you could try something like > this: > > (i) Create a new column of type numeric or integer as appropriate. > (ii) update your_table set new_column = CAST(trim(both ' 0' from > old_column) as numeric) > (iii) Drop the old column, as well as any constraints depending on it. > (iv) Rename the new column to the same name as the old column > (v) Recreate any of the constraints dropped in step (iii). > > I think the cast in step (ii) might not be necessary - not sure about this. Agreed. There's a slightly shorter way, you can do: ALTER TABLE tablename ALTER COLUMN columnname TYPE INT; If that doesn't work because the cast isn't automatic, you can add a USING clause: ALTER TABLE tablename ALTER COLUMN columnname TYPE INT USING columnname::INT; (as an example, the using clause may need to be more complicate than that). -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
