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?

I am trying to find a way to get this information to users. I have
modified command.c to output a different error message:

test=> alter table x add column z int default 4;
ERROR: Adding columns with defaults is not implemented because it
is unclear whether existing rows should have the DEFAULT value
or NULL. Add the column, then use ALTER TABLE SET DEFAULT.
You may then use UPDATE to give a non-NULL value to existing rows.

How does this sound? Peter, should I keep it for 7.3 so I don't mess up
the translations in 7.2?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026