Re: Inherited tables and NOT NULL (pg 7.2.1)
От | Luke Pascoe |
---|---|
Тема | Re: Inherited tables and NOT NULL (pg 7.2.1) |
Дата | |
Msg-id | 00b901c2c641$8be54eb0$3200000a@K2 обсуждение исходный текст |
Ответ на | Inherited tables and NOT NULL (pg 7.2.1) ("Luke Pascoe" <luke.p@kmg.co.nz>) |
Ответы |
Re: Inherited tables and NOT NULL (pg 7.2.1)
|
Список | pgsql-general |
> 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 > >
В списке pgsql-general по дате отправления: