On Thursday 17 December 2009, Antonio Goméz Soto <antonio.gomez.soto@xxxxxxxxx> wrote: > Hi, > > I am regularly altering tables, adding columns setting default values > etc. This very often takes a very long time and is very disk intensive, > and this gets pretty annoying. > > Things are hampered by the fact that some of our servers run PG 7.3 > > Suppose I have a table and I want to add a non NULL column with a default > value. What I normally do is: > > alter table person add column address varchar(64); > update person set address = '' where address IS NULL; > alter table person alter column address set not NULL; > alter table person alter column address set default ''; > > When the table contains millions of records this takes forever. > > Am I doing something wrong? Do other people have the same problems? > > Thanks, > Antonio You could speed it up: - drop all indexes on the table - alter table person add column address varchar(64) not null default '' - recreate the indexes It will require exclusive access to the table for the duration, but it'll be a lot faster and result in a lot less bloat than what you're doing now. It still has to rewrite the whole table, but it's a lot faster than UPDATE. (I have no idea if this works on 7.3). -- "No animals were harmed in the recording of this episode. We tried but that damn monkey was just too fast." -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
