X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A6315528BD for ; Tue, 28 Jun 2005 14:44:58 -0300 (ADT) 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 04216-03 for ; Tue, 28 Jun 2005 17:44:52 +0000 (GMT) Received: from mopsy2.musc.edu (mopsy2.musc.edu [128.23.34.56]) by svr1.postgresql.org (Postfix) with ESMTP id 2DEAF5281E for ; Tue, 28 Jun 2005 14:44:49 -0300 (ADT) Received: from revere3.musc.edu (revere3.musc.edu [128.23.203.9]) by mopsy2.musc.edu (8.12.10/8.12.10) with ESMTP id j5SHQgLn012197; Tue, 28 Jun 2005 13:26:42 -0400 Received: from cottontail.musc.edu (cottontail.musc.edu [128.23.203.163]) by revere3.musc.edu (8.12.9/8.12.9) with ESMTP id j5SHQfhk002189; Tue, 28 Jun 2005 13:26:41 -0400 (EDT) Received: from [128.23.116.246] (grid.135can.musc.edu [128.23.116.246]) (authenticated bits=0) by cottontail.musc.edu (8.12.10/8.12.10) with ESMTP id j5SHQfQm007326 (version=TLSv1/SSLv3 cipher=RC4-MD5 bits=128 verify=NOT); Tue, 28 Jun 2005 13:26:41 -0400 Message-ID: <42C18860.1010906@musc.edu> Date: Tue, 28 Jun 2005 13:26:56 -0400 From: Chuming Chen Reply-To: chen@musc.edu User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.7.8) Gecko/20050513 Fedora/1.7.8-2 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Martin Fandel Cc: pgsql-admin@postgresql.org Subject: Re: change existing table definition References: <42C15044.4030407@musc.edu> <1119974365.18693.16.camel@fandelm.ecommit.de> In-Reply-To: <1119974365.18693.16.camel@fandelm.ecommit.de> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-MUSC-MailScanner-Information: Please contact postmstr@musc.edu for more information X-MUSC-MailScanner: Found to be clean X-MUSC-MailScanner-SpamCheck: X-MailScanner-From: chen@musc.edu X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200506/269 X-Sequence-Number: 18024 Hi, Thanks a lot for your quick reply and help. The following is what I find from google. Will it work? A quicker solution would be to use the pg_dump command to dump the table, change the needed columns and restore everything. pg_dump -c -t > psql < Regards, Chuming Martin Fandel wrote: >Aehm sorry. ALTER TABLE is not only in PostgreSQL 8 ;). Only the >datatypes can be changed in postgres 8 (right?). I answered to fast. >I'm sorry. > >CREATE TABLE newtable ( "bla" varchar(50)); >CREATE INDEX/TRIGGER/... (with different names as the production table) >INSERT INTO newtable (select * from production); >ALTER TABLE production RENAME TO old; >ALTER TABLE newtable RENAME TO production; > >If this is working correctly, you can drop the old INDEXES and >rename them. > >If its not working correctly > >ALTER TABLE production RENAME TO new; >ALTER TABLE old RENAME TO production; > >and insert the different data (which is in the oldtable) into the >production table. > >Greetings, >Martin > > >Am Dienstag, den 28.06.2005, 17:29 +0200 schrieb Martin Fandel: > > >>Hi >> >>ALTER TABLE is only in PostgreSQL 8. But you can create a new table >>with varchar(50) and copy the data from the existing into the new >>table. How much relation_size has your table? Do you create the >>dbsize-functions which are included in the contrib package? >> >>Best regards, >>Martin >> >>Am Dienstag, den 28.06.2005, 10:39 -0400 schrieb Chuming Chen: >> >> >>>Peter Eisentraut wrote: >>> >>> >>> >>>>Chuming Chen wrote: >>>> >>>> >>>> >>>> >>>>>How can I change the column definition of an existing table, ie. from >>>>>varchar(30) to varchar(50)? Is there any way to add a new column to >>>>>an existing table? >>>>> >>>>> >>>>> >>>>> >>>>The ALTER TABLE command can do all that. You need version 8.0 or later >>>>for some functionality though. >>>> >>>> >>>> >>>> >>>> >>>Is there another way to do it in 7.* ? >>> >>>---------------------------(end of broadcast)--------------------------- >>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >>> >>> > > > > >