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

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. 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;

where the last command would verify that the column contains no nulls
before setting the flag, just like ALTER TABLE ADD CONSTRAINT does now
(but I think we don't have a variant for NULL/NOT NULL constraints).

This is slightly ugly, maybe, but it sure beats not having the feature
at all. Besides, it seems to me there are cases where you don't really
*want* the DEFAULT value to be used to fill the column, but something
else (or even want NULLs). Why should the system force an update of
every row in the table with a value that might not be what the user
wants?

regards, tom lane