Re: ALTER TABLE ADD COLUMN column SERIAL -- unexpected results

Tom Lane wrote:
> 
> Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> > Christopher Kings-Lynne wrote:
> >> Just out of interest, is there a special reason it's difficult to implement
> >> the DEFAULT feature of alter table add column?
> 
> > Without *DEFAULT* we don't have to touch the table file
> > at all. With *DEFAULT* we have to fill the new column
> > with the *DEFAULT* value for all existent rows.
> 
> Do we?  We could simply declare by fiat that the behavior of ALTER ADD
> COLUMN is to fill the new column with nulls.  Let the user do an UPDATE
> to fill the column with a default, if he wants to. 

I don't like to fill the column of the existent rows but
it seems to be the spec.

> After all, I'd not
> expect that an ALTER that adds a DEFAULT spec to an existing column
> would go through and replace existing NULL entries for me.
> 
> This is a little trickier if one wants to make a NOT NULL column,
> however.  Seems the standard technique for that could be
> 
>         ALTER tab ADD COLUMN newcol without the not null spec;
>         UPDATE tab SET newcol = something;
>         ALTER tab ALTER COLUMN newcol ADD CONSTRAINT NOT NULL;
> 

Yes I love this also.

regards,
Hiroshi Inoue