Thread: Changing datatype of a column
<br /><p><font face="Arial" size="2"> Hi,</font><p><font face="Arial" size="2"> I would like toKnow if there any way (and what is the command for that) to convert a column datatype</font><br /><font face="Arial" size="2"> from int4 to numeric(11) of an existing table that have data in it?</font><br /><font face="Arial"size="2"> </font><br /><font face="Arial" size="2"> </font><br /><font face="Arial" size="2"> Thanks,</font><br /><font face="Arial" size="2"> Ilan</font><p><font face="Arial" size="2"> </font><br /><b><font color="#000000" face="Century Gothic">_________________________________________</font></b><br/><b><font color="#000000" face="Century Gothic">ILAN FAIT</font></b><br/><font color="#000000" face="Century Gothic" size="2">Tel: 972-9-9519133 Ex.247 iWeb Technologies</font><br/><font color="#000000" face="Century Gothic" size="2">Fax: 972-9-9519134 91 MedinatHa'Yehudim St.</font><br /><font color="#000000" face="Century Gothic" size="2"> Herzliya 46120 IL</font><br /><font color="#000000" face="CenturyGothic" size="2"><a href="mailto:ilan@iweb.com">mailto:ilan@iweb.com</a> www.iweb.com</font><br />
Ilan Fait writes: > I would like to Know if there any way (and what is the command > for that) to convert a column datatype > from int4 to numeric(11) of an existing table that have data > in it? No, you have to recreate the table. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
There is no way to do this directly, but it is a pretty simple task to do this using SELECT INTO. Here is a trivial example of how to do this. First of all let's say you have a table that looks like this with some values in it: CREATE TABLE foo ( num int ); processdata=> select * from foo;num ----- 1 2 3 (3 rows) A simple query using SELECT INTO would give you the table that you need. processdata=> select num::numeric(11) as num into bar from foo; Here's the results. processdata=> \d bar Table "bar"Attribute | Type | Modifier -----------+---------------+----------num | numeric(11,0) | processdata=> select * from bar;num ----- 1 2 3 (3 rows) You can then drop the original table and use alter table to change the name of the new table. You can also add constraints and whatever else you need. Hope this is helpful, Jason --- Ilan Fait <ilan@iweb.com> wrote: > > Hi, > > I would like to Know if there any way > (and what is the command > for that) to convert a column datatype > from int4 to numeric(11) of an > existing table that have data > in it? > > > Thanks, > Ilan > > > _________________________________________ > ILAN FAIT > Tel: 972-9-9519133 Ex.247 iWeb Technologies > Fax: 972-9-9519134 91 Medinat > Ha'Yehudim St. > > Herzliya 46120 IL > mailto:ilan@iweb.com www.iweb.com > > > __________________________________________________ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/