Re: alter table performance

Antonio Goméz Soto wrote on 17.12.2009 22:26:
> 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?

What's wrong with:

alter table person add column address varchar(64) not null default '';

Although I don't know if such a pre-historic version like 7.3 would support that.

It works for 8.4 and I believe this was working with 8.3 and 8.2 as well

Thomas