Re: effective SELECT from child tables

On P, 2005-10-02 at 22:49 -0400, Greg Stark wrote:

> 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

Probably a different syntax would be better here, perhaps

ALTER TABLE ADD foo integer AS 1 WHEN MISSING;

or somesuch.

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

Not if you require these two to happen in one transaction to affect
added/missing values.

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