Re: effective SELECT from child tables

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:

> On Sun, Oct 02, 2005 at 11:51:27PM +0300, Hannu Krosing wrote:
> > Right. Actually the "default" value returned for missing columns should
> > be different from the default for new values and should be settable only
> > once, when adding the column, else issues would become really really
> > weird.
>
> Right, the only way I could really imagine it working is have a flag,
> attautodefault which if set would return the default instead of NULL.

No, I think redefining NULL is a non-starter. This whole thing only has legs
if Postgres can distinguish between a column that has never been set and a
column that has NULL.

Actually the only case where I don't see bizarre semantic consequences is the
case of a newly created column. 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. No question of
columns magically acquiring a value where they were NULL before.

The *main* reason I would be happy about this is that it would let me add NOT
NULL columns efficiently. I would expect NOT NULL to be allowed whenever a
default is provided since there's obviously no way it can cause a problem.

(I don't follow the "NULL is evil all the time" philosophy but I do try to set
columns NOT NULL wherever I can. It always annoys me that when adding a new
column I have to choose between a massive disk intensive batch job or
compromising the design.)

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.

--
greg