From ilan@iweb.com Tue Jun 26 07:39:23 2001 Received: from exchange.iweb.co.il ([212.68.138.186]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5Q7dMa87156 for ; Tue, 26 Jun 2001 03:39:23 -0400 (EDT) (envelope-from ilan@iweb.com) Received: by EXCHANGE with Internet Mail Service (5.5.2653.19) id ; Tue, 26 Jun 2001 10:41:04 +0300 Message-ID: From: Ilan Fait To: "'Pgsql-Sql (E-mail)" Subject: Changing datatype of a column Date: Tue, 26 Jun 2001 10:41:03 +0300 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2653.19) Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C0FE13.59F758F0" X-Archive-Number: 200106/409 X-Sequence-Number: 3706 This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C0FE13.59F758F0 Content-Type: text/plain; charset="windows-1255" 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 ------_=_NextPart_001_01C0FE13.59F758F0 Content-Type: text/html; charset="windows-1255" Content-Transfer-Encoding: quoted-printable Changing datatype of a column

       =        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-951= 9133  Ex.247    iWeb Technologies
Fax: 972-9-951= 9134            = ;    91 Medinat Ha'Yehudim St.
  &n= bsp;            = ;            &n= bsp;            = ;             H= erzliya 46120 IL
mailto:ilan@iweb.com    www.iweb.com<= /FONT>


------_=_NextPart_001_01C0FE13.59F758F0-- From peter_e@gmx.net Tue Jun 26 14:42:17 2001 Received: from mailout00.sul.t-online.de (mailout00.sul.t-online.com [194.25.134.16]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5QEgHa87121 for ; Tue, 26 Jun 2001 10:42:17 -0400 (EDT) (envelope-from peter_e@gmx.net) Received: from fwd01.sul.t-online.de by mailout00.sul.t-online.de with smtp id 15Eu2S-0002FZ-05; Tue, 26 Jun 2001 16:42:00 +0200 Received: from peter.localdomain (520083510237-0001@[217.80.146.16]) by fmrl01.sul.t-online.com with esmtp id 15Eu2H-1QkfgWC; Tue, 26 Jun 2001 16:41:49 +0200 Date: Tue, 26 Jun 2001 16:43:54 +0200 (CEST) From: Peter Eisentraut To: Ilan Fait cc: "'Pgsql-Sql (E-mail)" Subject: Re: Changing datatype of a column In-Reply-To: Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Sender: 520083510237-0001@t-dialin.net X-Archive-Number: 200106/418 X-Sequence-Number: 3715 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 From jdearl@yahoo.com Tue Jun 26 16:28:36 2001 Received: from web10001.mail.yahoo.com (web10001.mail.yahoo.com [216.136.130.37]) by postgresql.org (8.11.3/8.11.1) with SMTP id f5QGSaa41917 for ; Tue, 26 Jun 2001 12:28:36 -0400 (EDT) (envelope-from jdearl@yahoo.com) Message-ID: <20010626162822.90915.qmail@web10001.mail.yahoo.com> Received: from [12.18.144.101] by web10001.mail.yahoo.com; Tue, 26 Jun 2001 09:28:22 PDT Date: Tue, 26 Jun 2001 09:28:22 -0700 (PDT) From: Jason Earl Subject: Re: Changing datatype of a column To: Ilan Fait , "'Pgsql-Sql \(E-mail\)" In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Archive-Number: 200106/421 X-Sequence-Number: 3718 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 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/