Inherited tables and NOT NULL (pg 7.2.1)

Luke Pascoe <luke.p@kmg.co.nz> wrote:

temp=> ALTER TABLE Parent ADD CONSTRAINT ddd_nn CHECK (ddd IS NOT
NULL); ERROR: AlterTableAddConstraint: rejected due to CHECK
constraint ddd_nn

Why can't I add this check to the Parent table?

Try "CHECK (NOT (ddd IS NULL))" or "CHECK (NOT ddd IS NULL)" instead...
It might be some interpretation differances between your head and pgsql
:)
I know i have these types of constraints myself, i can dig up the
specifics if the above doesn't work.

Cheers
Magnus

"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

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

"Luke Pascoe" <luke.p@kmg.co.nz> writes:

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

What version did you say you were running? It works fine for me in 7.3.
I can believe this has been broken in the past, though.

regards, tom lane

7.2.1 - I guess I should upgrade/build, but I've been made slack by apt :-)

----- 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: Tuesday, January 28, 2003 10:09 AM
Subject: Re: [GENERAL] Inherited tables and NOT NULL (pg 7.2.1)