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.