Re: Inherited tables and NOT NULL (pg 7.2.1)

> Could you provide a complete example? There must be something odd about
> the parent or child table schemas, which you have not showed us.

Wierd, I had dropped the temporary database I was playing with last week, so
I tried to reproduce the problem...I still can't add the CHECK but I'm
getting a different error. Full log follows:

temp=> CREATE TABLE parent (
temp(> aaa INT NOT NULL
temp(> );
CREATE
temp=> CREATE TABLE child (
temp(> bbb INT NOT NULL
temp(> ) INHERITS (parent);
CREATE
temp=> INSERT INTO child (aaa, bbb) VALUES (111, 111);
INSERT 70027 1
temp=> SELECT * FROM child;
aaa | bbb
-----+-----
111 | 111
(1 row)

temp=> SELECT * FROM parent;
aaa
-----
111
(1 row)

temp=> ALTER TABLE parent ADD ccc INT;
ALTER
temp=> UPDATE parent SET ccc = 0;
UPDATE 1
temp=> ALTER TABLE parent ALTER COLUMN ccc SET DEFAULT 0;
ALTER
temp=> ALTER TABLE parent ADD CONSTRAINT ccc_nn CHECK (ccc IS NOT NULL);
ERROR: cache lookup of attribute 3 in relation 70023 failed
temp=>

I'm pretty sure that's exactly what I was doing before, very simple tables
to test ideas.

----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Luke Pascoe" <luke(dot)p(at)kmg(dot)co(dot)nz>
Cc: "pgsql-general" <pgsql-general(at)postgresql(dot)org>
Sent: Friday, January 24, 2003 6:41 PM
Subject: Re: [GENERAL] Inherited tables and NOT NULL (pg 7.2.1)

> "Luke Pascoe" <luke(dot)p(at)kmg(dot)co(dot)nz> writes:
> > Well I was trying out adding a new column to my "Parent" table, I wanted
a
> > not null, defaulted, integer column, so I did:
>
> > temp=> ALTER TABLE Parent ADD ddd INT;
> > ALTER
> > temp=> UPDATE Parent SET ddd = 0;
> > UPDATE 2
> > temp=> ALTER TABLE Parent ALTER COLUMN ddd SET DEFAULT 0;
> > ALTER
> > temp=> ALTER TABLE Parent ADD CONSTRAINT ddd_nn CHECK (ddd IS NOT NULL);
> > ERROR: AlterTableAddConstraint: rejected due to CHECK constraint ddd_nn
>
> [ scratches head ... ] That looks like it should work. The ALTER ADD
> COLUMN should have recursively added the column to all the child tables
> too, and the UPDATE should have recursively hit all the children. So
> there should be no rows left anywhere where the constraint could fail.
>
> Could you provide a complete example? There must be something odd about
> the parent or child table schemas, which you have not showed us.
>
> regards, tom lane
>
>