Thread: Inherited tables and NOT NULL (pg 7.2.1)

Inherited tables and NOT NULL (pg 7.2.1)

From
"Luke Pascoe"
Date:
I'm playing around with inherited tables to see if they're suitable for a
task I'm about to begin.

I want to create 6 tables, all with the same columns except 1. They will
_always_ be this way, ie. any new column added will be added to all. Sounds
perfect for inherited tables right?

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

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

========================================
Luke Pascoe
Senior Developer / Systems administrator
KMG (NZ) Limited. http://www.kmg.co.nz
Mobile: (021) 303019
Email: luke.p@kmg.co.nz
========================================



Re: Inherited tables and NOT NULL (pg 7.2.1)

From
"Magnus Naeslund(f)"
Date:
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


Re: Inherited tables and NOT NULL (pg 7.2.1)

From
Tom Lane
Date:
"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

Re: Inherited tables and NOT NULL (pg 7.2.1)

From
"Luke Pascoe"
Date:
> 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
>
>



Re: Inherited tables and NOT NULL (pg 7.2.1)

From
Tom Lane
Date:
"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

Re: Inherited tables and NOT NULL (pg 7.2.1)

From
"Luke Pascoe"
Date:
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)


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