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

Tom Lane wrote:
>
> Hiroshi Inoue <Inoue(at)tpf(dot)co(dot)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