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@sss.pgh.pa.us>
To: "Luke Pascoe" <luke.p@kmg.co.nz>
Cc: "pgsql-general" <pgsql-general@postgresql.org>
Sent: Friday, January 24, 2003 6:41 PM
Subject: Re: [GENERAL] Inherited tables and NOT NULL (pg 7.2.1)


> "Luke Pascoe" <luke.p@kmg.co.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
>
>