Search Postgresql Archives

Re: best practice in upgrading db structure

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Tuesday 28 March 2006 17:31, Jim C. Nasby wrote:
> On Wed, Mar 29, 2006 at 12:24:04AM +0200, SunWuKung wrote:
> > In article <20060328221109.GC75181@xxxxxxxxxxxxx>, jnasby@xxxxxxxxxxxxx
> > > On Tue, Mar 28, 2006 at 09:28:12PM +0200, SunWuKung wrote:
> > > > This is going to be an amateur question...
> > > >
> > > > Could somebody explain me, or point me to a resource where I can find
> > > > out what is the recommended practice when a live db needs to be
> > > > replaced with a new version of it that has a slightly different
> > > > structure?
> > > >
> > > > My first guess would be to create the empty new version of the db and
> > > > insert the data of the old one into it - adding and modifying it when
> > > > necessary, but I am not sure.
> > > >
> > > > What do you usually do in a situation like this?
> > >
> > > ALTER TABLE ...
> >
> > I know this is easy to do when the db is empty, but do you still suggest
> > this when there is data in the db : changing the structure of the old
> > version of the db to match the new one (obviously not on the live
> > server) and than try to modify the data in it to fit the structure eg.
> > there are two new columns that needs data?
>
> Depends on how much data you need to modify. For small tables, I stick
> with ALTER TABLE because it's a lot cleaner/easier. For larger tables,
> you might want to CREATE TABLE AS SELECT ..., or maybe copy out and copy
> back in.

This seems backwards to me. On larger tables I tend to favor alter table for 
adding/dropping columns since the table doesn't need to be rewritten, and on 
smaller tables I'd be more likely to use CTAS (although even then still 
pretty unlikely)

-- 
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux