Re: feature request - adding columns with default value

** Reply to message from Dennis Gearon <gearond(at)cvc(dot)net> on Fri, 04 Apr 2003
08:19:24 -0800
Yeah,
Db2 does.
The reall usefulness of this is the form :-
Alter table add blah varchar(10) not null default 'No'

The backend appears to initialise the new column to the default value (just
about instantly - even for large tables), and you can add a not null column to
the table in one statement.

Regards,
Wayne

> do ANY databases do this? How does it fit the SQL standard. And, whether you do
> it manually or the ALTER TABLE command does it, it's still going to block other
> updates.
>
> Henrik Steffen wrote:
> > hello,
> >
> > when doing an "alter table blub add column blah int2"
> > and then issuing an "alter table blub alter column blah set default 0"
> > I will always have to manually "update blub set blah=0" to initialize
> > all existing records.... this is quite annoying while performing this
> > update on a running database with 100 simultaneous clients accessing
> > the database and about 443482 rows in the table.
> >
> > it takes about 10 minutes to perform this update, and for about 6 minutes
> > all other updates to the table are in status "waiting", this leads to
> > a very high load and all max_connection setting is reached soon
> > (on a Dual-Xeon 2.4 GHz machine with 2 GB RAM)
> >
> > i would suggest to change the "alter table add column" function to
> > adding a default value while adding the column if this is possible
> > and if this minimizes "down-time".
> >
> > thank you
> >
> > --
> >
> > Mit freundlichem Gruß
> >
> > Henrik Steffen
> > Geschäftsführer
> >
> > top concepts Internetmarketing GmbH
> > Am Steinkamp 7 - D-21684 Stade - Germany
> > --------------------------------------------------------
> > http://www.topconcepts.com Tel. +49 4141 991230
> > mail: steffen(at)topconcepts(dot)com Fax. +49 4141 991233
> > --------------------------------------------------------
> > 24h-Support Hotline: +49 1908 34697 (EUR 1.86/Min,topc)
> > --------------------------------------------------------
> > Ihr SMS-Gateway: JETZT NEU unter: http://sms.city-map.de
> > System-Partner gesucht: http://www.franchise.city-map.de
> > --------------------------------------------------------
> > Handelsregister: AG Stade HRB 5811 - UstId: DE 213645563
> > --------------------------------------------------------
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html