Postgres Pro
Downloads
Home   >   mailing lists

Thread: Table Copy.

Table Copy.

From
PostgreSQL Server
Date:
HI!

I'm new to postgres. I need to have a table as a copy of another one.

Example:

CREATE TABLE one (       fileda INTEGER,       filedb INTEGER,       filedc INTEGER );

CREATE TABLE two (       fileda INTEGER,       filedb INTEGER,       filedc INTEGER );

As on insert to table one I should get the same insert on table two.
As on delete to table one I should get the same delete on table two.
As on update to table one I should get the same update on table two.

Can someone provide the examples i can study ?

Thanks in advance.

Alex

Re: Table Copy.

From
"Michael Paesold"
Date:
Alex wrote:
> HI!
> 
> I'm new to postgres. I need to have a table as a copy of another one.
> 
> Example:
> 
> CREATE TABLE one (
>         fileda INTEGER,
>         filedb INTEGER,
>         filedc INTEGER );
> 
> CREATE TABLE two (
>         fileda INTEGER,
>         filedb INTEGER,
>         filedc INTEGER );
> 
> As on insert to table one I should get the same insert on table two.
> As on delete to table one I should get the same delete on table two.
> As on update to table one I should get the same update on table two.
> 
> Can someone provide the examples i can study ?

You could do it with RULEs. Here is an example how you would do the DELETE.

CREATE RULE copy_one_to_two AS ON DELETE TO one DO   DELETE FROM two   WHERE two.fileda=OLD.fileda     AND
two.filedb=OLD.filedb    AND two.filedc=OLD.filedc;
 

You should change the where clause if you have a primary key on that table.
I am presuming fileda/filedb/filedc are unique in combination...
Read the section of the docs about RULEs for INSERT and UPDATE examples.

Regards,
Michael Paesold

Re: Table Copy.

From
Dmitry Tkach
Date:
what about CREATE TABLE one (         fileda INTEGER,         filedb INTEGER,         filedc INTEGER );  CREATE VIEW
twoAS SELECT * FROM one;
 
?

Dima

PostgreSQL Server wrote:
> HI!
> 
> I'm new to postgres. I need to have a table as a copy of another one.
> 
> Example:
> 
> CREATE TABLE one (
>         fileda INTEGER,
>         filedb INTEGER,
>         filedc INTEGER );
> 
> CREATE TABLE two (
>         fileda INTEGER,
>         filedb INTEGER,
>         filedc INTEGER );
> 
> As on insert to table one I should get the same insert on table two.
> As on delete to table one I should get the same delete on table two.
> As on update to table one I should get the same update on table two.
> 
> Can someone provide the examples i can study ?
> 
> Thanks in advance.
> 
> Alex
> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

Re: Table Copy.

From
"Christopher Kings-Lynne"
Date:
> As on insert to table one I should get the same insert on table two.
> As on delete to table one I should get the same delete on table two.
> As on update to table one I should get the same update on table two.
> 
> Can someone provide the examples i can study ?

Look up 'CREATE TRIGGER' and 'CREATE RULE' in the postgres documentation.

Chris