[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] best practice in upgrading db structure
From:       Jim Nasby <jnasby () pervasive ! com>
Date:       2006-03-31 19:29:56
Message-ID: 5D50A2A4-AC10-4AF1-8096-219B1587533F () pervasive ! com
[Download RAW message or body]


On Mar 28, 2006, at 8:40 PM, Robert Treat wrote:
>> 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)

It depends on what exactly you're doing. For example, if you're  
adding a new field and have to populate it with data, you end up  
rewriting the entire table, but in a way that leads to considerable  
bloat. Of course if you can get away without re-writing the entire  
table you absolutely want to go that route.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly
[prev in list] [next in list] [prev in thread] [next in thread]