Обсуждение: How to change data type in column ?
Hi,
How can I change a data type in column ?
Thnx.
On Tuesday 15 Apr 2003 10:36 am, Vitali Djatsuk wrote: > Hi, > > How can I change a data type in column ? Well, the closest you could get would be something like: ALTER TABLE RENAME column TO backup_column; ALTER TABLE ADD COLUMN column ...; ...copy and transform data... ALTER TABLE DROP COLUMN backup_column; Personally, I tend to dump/recreate the table where possible, but I might be a little paranoid there. I think you'll need to recreate any triggers/FK's in either case. -- Richard Huxton
On Tue, 15 Apr 2003, Vitali Djatsuk wrote: > > Hi, > > How can I change a data type in column ? There are a lot of issues involved in changing the type. I.e. what should the database do to "coerce" the data from one type to another that's already in the table. should the misformed data in the input be simply dropped and a null inserted, what if it's not null and doesn't fit, do we toss the row? not convert? The best way to get around these problems is to select the fields into a new table and coerce the type as you do it. That way, you can see where you're going before you get there. create table test (name text, id int, balance numeric (12,2), num float); insert some data... select name, id, balance::float, num::numeric(12,2) into newtable; Now you can check newtable, and if it's right you can then delete the old table and rename newtable in its place.
> On Tue, 15 Apr 2003, Vitali Djatsuk wrote: > >> >> Hi, >> >> How can I change a data type in column ? > > There are a lot of issues involved in changing the type. I.e. what should the database do to > "coerce" the data from one type to another that's already in the table. should the misformed > data in the input be simply dropped and a null inserted, what if it's not null and doesn't > fit, do we toss the row? not convert? > The best way to get around these problems is to select the fields into a new table and coerce > the type as you do it. That way, you can see where you're going before you get there. > > create table test (name text, id int, balance numeric (12,2), num float); insert some data... > select name, id, balance::float, num::numeric(12,2) into newtable; > > Now you can check newtable, and if it's right you can then delete the old table and rename > newtable in its place. Instead of deleting the table Why not add a new column of the required type , update it with the values from old column , drop the old column and rename the new col to the old col . Considering the fact that he already got too many references it may be easier to avoid drop the table altogether. regds mallah. > > > ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off > all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) ----------------------------------------- Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/