From: | Jeff Hoffmann <jeff(at)propertykey(dot)com> |
---|---|
To: | amy cheng <amycq(at)hotmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: [GENERAL] shutdown gracefully & single user mode? |
Date: | 1999-09-14 15:21:53 |
Message-ID: | 37DE6811.C98569C6@propertykey.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
amy cheng wrote:
>
> hi, all experts there, greetings!
>
> Just minutes ago, my boss found out one of the attributes in a
> table is too short (varchar 64 for url), we need to make
> it wider to 85 A.S.A.P. Seems that alter table can not do it.
> So, I used pg_dump, (how to do it gracefully?) immediately drop the table,
> shutdown the postmaster (not necessary?) and change the dumped table, and
> then restart the postmaster, use pgsql to reload the data. Our database is
> not large YET. So, it took ONLY 10 minutes to re-load.
>
the way i would do this is add a new column to the table that's the
right size (url2), copy the contents of url1 to url2, rename the
original table to a new table name, then select all the fields except
the original url field into a new table (called the same as the original
one, which is now named something else). it sounds a lot more
complicated than it is. maybe this will help:
alter table mytable add column url2 varchar(85);
update mytable set url2=url;
alter table mytable rename to mytable2;
select field1, field2, field3, url2 as url into mytable from mytable2;
probably will save some time, plus i've always though dumping tables was
a PITA.
jeff
From | Date | Subject | |
---|---|---|---|
Next Message | Teodor Cimpoesu | 1999-09-14 16:11:38 | Re: [GENERAL] shutdown gracefully & single user mode? |
Previous Message | Bryan White | 1999-09-14 15:20:30 | Re: [GENERAL] shutdown gracefully & single user mode? |