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

> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > I am trying to find a way to get this information to users. I have
> > modified command.c to output a different error message:

I should have used different wording here. I meant I tested a
modification to command.c.

> > 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.
>
> Kindly put the error message back as it was.
>
> It's not "unclear" what the command should do; SQL92 is perfectly
> clear about it.
>
> I would also remind you that we've got quite a few sets of error message
> translations in place now. Gratuitous changes to message wording in the
> last week of beta are *not* appropriate, because they break all the
> translations.

If you read a little further you would have seen:

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

I was not about to apply it. I need comments on how we should
communicate this to the user. I have email from you from July saying:

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

Then Hiroshi saying:

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

So, are we now all agreed that we have to fill in the existing rows with
the default value? If so, I can document that in the TODO list and
discard this patch.

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