Re: Happy column adding (was RE: [HACKERS] Happy column dropping)

Hiroshi Inoue wrote:

> > -----Original Message-----
> > From: owner-pgsql-hackers@postgreSQL.org
> > [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Don Baccus
> >
> > At 11:55 AM 1/25/00 +0100, Peter Eisentraut wrote:
> > >On Tue, 25 Jan 2000, Hiroshi Inoue wrote:
> > >
> > >> Even default is not allowed in ADD COLUMN now.
> > >> There may be other reasons why they aren't allowed.
> > >
> > >It's not a matter of *allowed*, it's a parsing deficiency. The fact that
> > >there was a default declared gets silently ignored. If y'all allow ;) I
> > >would like to fix that (have already started a bit) by perusing the code
> > >in parse_func.c:transformCreateStmt and do the same for the alter table
> > >add column part. Maybe and add/drop constraint will come out in
> > the end as
> > >well.
> >
> > However, heap_getattr still won't see the default since it simply
> > checks to see of the attribute number falls off the end of the
> > tuple and then returns null.
> >
>
> Sorry,the following question might be already answered but too
> many postings for me.
>
> Do we have to refer default value for already inserted rows ?
> Doesn't 'default' have its meaning only when rows are about to be
> inserted ?
>

Of course yes.
from  "A guide to SQL Standard" page 106:

"ALTER TABLE S ADD COLUMN DISCOUNT SMALLINT DEFAULT -1

this statement adds a new fifth column called DISCOUNT to base table S. All
existing S rows are extended to include a value for new column; that value is
-1 in every such row...."

The problem is when we define a constraint for the column like:

ALTER TABLE S ADD COLUMN DISCOUNT SMALLINT NOT NULL

In such case IMO this should be refused because it violates data integrity,
an less you define also a default value for the column as in:

ALTER TABLE S ADD COLUMN DISCOUNT SMALLINT NOT NULL DEFAULT -1

José






>
> Regards.
>
> Hiroshi Inoue
> Inoue@tpf.co.jp
>
> ************