Re: effective SELECT from child tables

On P, 2005-10-02 at 23:00 -0400, Tom Lane wrote:
> Greg Stark <gsstark(at)mit(dot)edu> writes:
> > It would be nice to be able to do:
> > ALTER TABLE ADD foo integer DEFAULT 1
> > And there's no question of what what the semantics of this are.
>
> Sure, but you can only optimize this if the default expression is
> immutable...
>
> > On the other hand if you do
> > ALTER TABLE ADD foo integer
> > and then later do
> > ALTER TABLE ALTER foo SET DEFAULT 1
> > then there is a window where all those foos are NULL and then they magically
> > become 1? That doesn't seem tenable.
>
> It'd also be contrary to the SQL spec, AFAICS.
>
> Here's another interesting case to think about:
>
> ALTER TABLE ADD foo integer DEFAULT 1
> ...
> ALTER TABLE ALTER foo SET DEFAULT 2
>
> You'll have to pay the table-traversal cost on one step or the other.

The second, ALTER ... SET DEFAULT, would only set default for newly
inserted columns, not the ones which are missing due to tuples being
created before the column existed.

But completely different syntax may be more clear.

ALTER TABLE ADD foo integer WITH DEFAULT 1;

Or whatever

--
Hannu Krosing <hannu(at)skype(dot)net>