Re: keeping 3 tables in sync w/ each other

On Tue, 2007-09-18 at 09:56 +0100, Filip Rembiałkowski wrote:
> 2007/9/18, Ow Mun Heng <Ow(dot)Mun(dot)Heng(at)wdc(dot)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.