Thread: Inherited tables and NOT NULL (pg 7.2.1)
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 ========================================
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 > > 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 > >
"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) > "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 > >