Skip site navigation (1) Skip section navigation (2)

Re: change existing table definition

From: Chuming Chen <chen(at)musc(dot)edu>
To: Martin Fandel <martin(dot)fandel(at)alphyra-evs(dot)de>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: change existing table definition
Date: 2005-06-28 17:26:56
Message-ID: 42C18860.1010906@musc.edu (view raw or flat)
Thread:
Lists: pgsql-admin
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 <table name> <database> > <dumpfile>
psql <database> < <dumpfile>


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(at)postgresql(dot)org
>>>      
>>>
>
>
>
>  
>


In response to

Responses

pgsql-admin by date

Next:From: Martin FandelDate: 2005-06-28 20:04:39
Subject: Re: change existing table definition
Previous:From: Martin FandelDate: 2005-06-28 15:59:24
Subject: Re: change existing table definition

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group