From sandhyar@amiindia.co.in Tue Nov 15 11:44:31 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 04713DA2EF for ; Tue, 15 Nov 2005 07:44:31 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 18872-08 for ; Tue, 15 Nov 2005 11:44:33 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mars.amiindia.co.in (unknown [203.199.198.229]) by svr1.postgresql.org (Postfix) with ESMTP id B4E9DD6D8C for ; Tue, 15 Nov 2005 07:44:22 -0400 (AST) Received: from amiindiahjwbgh ([10.0.0.205]) (authenticated bits=0) by mars.amiindia.co.in (8.12.11/8.12.11) with ESMTP id jAFBqYws019295; Tue, 15 Nov 2005 17:22:34 +0530 Message-ID: <03e901c5e9db$06ba00d0$cd00000a@amiindiahjwbgh> From: "sandhya" To: "postgre" Cc: "sandhya" Subject: Reg: Changing Column type Date: Tue, 15 Nov 2005 17:22:16 +0530 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_03E6_01C5EA09.20584C30" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.001 required=5 tests=[HTML_MESSAGE=0.001] X-Spam-Score: 0.001 X-Spam-Level: X-Archive-Number: 200511/182 X-Sequence-Number: 19555 This is a multi-part message in MIME format. ------=_NextPart_000_03E6_01C5EA09.20584C30 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, When i am tring to change the column type from character/Varchar to = "int" i am getting error like, ERROR: column "fname" cannot be cast to type "int4" I can able to change the column type to "int " if the old datatype is = double etc. Even a "int" datatype also i can able to conver to "char" and why not viceversa....Please tell me where the problem is. Suggest me what to do to convert a "char" datatype to "int". Thanks&Regards, Sandhya ------=_NextPart_000_03E6_01C5EA09.20584C30 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi,
 
When i am tring to change the column = type from=20 character/Varchar to "int" i am getting error like,
 
ERROR:  column "fname" cannot be = cast to type=20 "int4"
 
I can able to change the column type to = "int " if=20 the old datatype is double etc.
Even a "int" datatype also i can able = to conver to=20 "char"
and why not viceversa....Please tell me = where the=20 problem is.
Suggest me what to do to convert a = "char" datatype=20 to "int".
 
Thanks&Regards,
Sandhya
 

 
 
------=_NextPart_000_03E6_01C5EA09.20584C30-- From tgl@sss.pgh.pa.us Tue Nov 15 14:32:27 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9E8E7DB6AA for ; Tue, 15 Nov 2005 10:32:27 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 36380-08 for ; Tue, 15 Nov 2005 14:32:30 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 379FFD71C1 for ; Tue, 15 Nov 2005 10:32:24 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id jAFEWMll010417; Tue, 15 Nov 2005 09:32:24 -0500 (EST) To: "sandhya" cc: "postgre" Subject: Re: Reg: Changing Column type In-reply-to: <03e901c5e9db$06ba00d0$cd00000a@amiindiahjwbgh> References: <03e901c5e9db$06ba00d0$cd00000a@amiindiahjwbgh> Comments: In-reply-to "sandhya" message dated "Tue, 15 Nov 2005 17:22:16 +0530" Date: Tue, 15 Nov 2005 09:32:22 -0500 Message-ID: <10416.1132065142@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200511/185 X-Sequence-Number: 19558 "sandhya" writes: > When i am tring to change the column type from character/Varchar to = > "int" i am getting error like, > ERROR: column "fname" cannot be cast to type "int4" If this is a pre-8.0 PG version, try casting to text and then int4. If 8.0 or later, it should work as is --- would you show us exactly what you did? regards, tom lane From sandhyar@amiindia.co.in Wed Nov 16 12:59:59 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 0B25DDA387 for ; Wed, 16 Nov 2005 08:59:59 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 02655-01 for ; Wed, 16 Nov 2005 13:00:01 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mars.amiindia.co.in (unknown [203.199.198.229]) by svr1.postgresql.org (Postfix) with ESMTP id AD257DB927 for ; Wed, 16 Nov 2005 08:59:53 -0400 (AST) Received: from amiindiahjwbgh ([10.0.0.205]) (authenticated bits=0) by mars.amiindia.co.in (8.12.11/8.12.11) with ESMTP id jAGD88oD030248; Wed, 16 Nov 2005 18:38:08 +0530 Message-ID: <030901c5eaae$bec0c280$cd00000a@amiindiahjwbgh> From: "sandhya" To: "postgre" Cc: "sandhya" Subject: Fw: Reg: Changing Column type Date: Wed, 16 Nov 2005 18:37:49 +0530 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.001 required=5 tests=[AWL=0.001] X-Spam-Score: 0.001 X-Spam-Level: X-Archive-Number: 200511/204 X-Sequence-Number: 19577 > Hi all, > I have PostGreSql -8 .2.0 version only. > > say i have a table(sample) with 2 columns like: > name varchar(20),age int; > > But when i give ALTER TABLE sample ALTER COLUMN age TYPE char; > It is working fine. > > BUT when i try to do the same for converting the "char/text" type data to > "int" still i am getting error as, > > like,ALTER TABLE sample ALTER COLUMN age TYPE int; > > ERROR: column "age" cannot be cast to type "pg_catalog.int4" > > Pls tell me how to do this.Am i wrong some where.If So,suggest me. > > Thanks&Regards > Sandhya R > > > > > > ----- Original Message ----- > From: "Tom Lane" > To: "sandhya" > Cc: "postgre" > Sent: Tuesday, November 15, 2005 8:02 PM > Subject: Re: [ADMIN] Reg: Changing Column type > > > > "sandhya" writes: > > > When i am tring to change the column type from character/Varchar to = > > > "int" i am getting error like, > > > ERROR: column "fname" cannot be cast to type "int4" > > > > If this is a pre-8.0 PG version, try casting to text and then int4. > > > > If 8.0 or later, it should work as is --- would you show us exactly > > what you did? > > > > regards, tom lane > From bruno@wolff.to Wed Nov 16 19:32:25 2005 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 06108D6D50 for ; Wed, 16 Nov 2005 15:32:25 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 60778-02 for ; Wed, 16 Nov 2005 19:32:24 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from wolff.to (wolff.to [66.93.197.194]) by svr1.postgresql.org (Postfix) with SMTP id E7FBFD6D16 for ; Wed, 16 Nov 2005 15:32:21 -0400 (AST) Received: (qmail 10844 invoked by uid 500); 16 Nov 2005 19:35:24 -0000 Date: Wed, 16 Nov 2005 13:35:24 -0600 From: Bruno Wolff III To: sandhya Cc: postgre Subject: Re: Reg: Changing Column type Message-ID: <20051116193524.GA4302@wolff.to> Mail-Followup-To: Bruno Wolff III , sandhya , postgre References: <03e901c5e9db$06ba00d0$cd00000a@amiindiahjwbgh> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <03e901c5e9db$06ba00d0$cd00000a@amiindiahjwbgh> User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200511/216 X-Sequence-Number: 19589 On Tue, Nov 15, 2005 at 17:22:16 +0530, sandhya wrote: > Hi, > > When i am tring to change the column type from character/Varchar to "int" i am getting error like, > > ERROR: column "fname" cannot be cast to type "int4" > > I can able to change the column type to "int " if the old datatype is double etc. > Even a "int" datatype also i can able to conver to "char" > and why not viceversa....Please tell me where the problem is. > Suggest me what to do to convert a "char" datatype to "int". One way to do this is to add a new int column. Use a query to insert data into the new column using to_number allow with whatever you need to handle strings that aren't numbers. Then delete the old column. Then rename the new column to be the original name. In at least 8.1 there is a USING clause which allows you to specify a conversion function which lets you do the above in one step. I don't know when that feature was added though.