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

> Bruce Momjian <pgman@candle.pha.pa.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@candle.pha.pa.us               |  (610)
853-3000+  If your life is a hard drive,     |  830 Blythe Avenue +  Christ can be your backup.        |  Drexel Hill,
Pennsylvania19026