Re: keeping 3 tables in sync w/ each other

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

--
Filip Rembiałkowski