Re: keeping 3 tables in sync w/ each other
От | Ow Mun Heng |
---|---|
Тема | Re: keeping 3 tables in sync w/ each other |
Дата | |
Msg-id | 1190195104.15764.22.camel@neuromancer.home.net обсуждение исходный текст |
Ответ на | Re: keeping 3 tables in sync w/ each other ("Filip Rembiałkowski" <plk.zuber@gmail.com>) |
Ответы |
Re: keeping 3 tables in sync w/ each other
("Filip Rembiałkowski" <plk.zuber@gmail.com>)
|
Список | pgsql-general |
On Tue, 2007-09-18 at 09:56 +0100, Filip Rembiałkowski wrote: > 2007/9/18, Ow Mun Heng <Ow.Mun.Heng@wdc.com>: > > Hi, > > > > I have 3 tables > > > > foo > > foo_loading_source1 > > foo_loading_source2 > > > > which is something like > > > > create table foo (a int, b int, c int) > > create table foo_loading_source1 (a int, b int, c int) > > create table foo_loading_source2 (a int, b int, c int) > > > > Is there a way which can be made easier to keep these 3 tables DDL in > > sync? > > > > the loading_sourceX tables are just a temporary-in-transit table for > > data \copy'ied into the DB before being inserted into the main foo > > table. > > > > Currently, each time I add a new column to foo, I have to "remember" to > > add the same to the other 2 table. > > > > Can I use inheritance? References? > > Inheritance might work in this case. But it will be a bit weird, > because you will see non-constraint data in parent unless you will > SELECT ... FROM ONLY parent > > > Try this example: > > create table parent ( id serial, data1 text ); > create table child () inherits( parent ); > \d child > alter table only parent add check ( data1 like '%fits parent' ); > insert into parent(data1) select 'this data fits parent'; > insert into child(data1) select 'this data was inserted to child'; > select * from parent; > select * from only parent; > select * from child; > alter table parent add column data2 text default 'new column default'; > \d child Nope. Doesn't work as it should be. Note : I've removed the check as well as it's not needed for my purpose. create table parent ( id int primary key, data1 text ); create table child () inherits( parent ); insert into parent(id,data1) values (1,'parent1'); insert into parent(id,data1) values (2,'parent2'); insert into parent(id,data1) values (3,'parent3'); insert into parent(id,data1) values (4,'parent4'); insert into child(id,data1) values (6,'child1-bastard'); insert into child(id,data1) values (7,'child2-bastard'); insert into child(id,data1) values (8,'child3-bastard'); insert into child(id,data1) values (9,'child4-bastard'); => select * from parent; id | data1 ----+---------------- 1 | parent1 2 | parent2 3 | parent3 4 | parent4 6 | child1-bastard 7 | child2-bastard 8 | child3-bastard 9 | child4-bastard 1 | parent1-new simulate a delete => delete from parent where id in (select id from child); DELETE 6 => select * from parent; id | data1 ----+--------- 2 | parent2 3 | parent3 4 | parent4 => select * from child; id | data1 ----+------- (0 rows) Doesn't do what I want which is to use the child table as a temp holding ground prior to data insertion into parent table.
В списке pgsql-general по дате отправления: