Re: best practice in upgrading db structure

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(dot)GC75181(at)pervasive(dot)com>, jnasby(at)pervasive(dot)com
> > > 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