Обсуждение: copying data between tables
Hi all! I have the following task : I have some tables edited (insert/update) by users. I should create a file (in whatever format) containing changes of the last day,every day.What I have currently is : I have two tables of the same structer ,every day i : select * from newtable except select * from oldtable And then format the data with perl script.After that ,i am copying the data from the new table to the old table .It works pretty good except that the copying is VERY slow: I use : insert into old from (select * from newtable); Is there any more afficient way to do what i need , that somebody already use? Is there any more afficient way to copy data between tables? I know that i can use COPY to copy files from and to files ,can it be used here?
Le ven 27/02/2004 à 03:40, Tsirkin Evgeny a écrit : > Hi all! > I have the following task : > I have some tables edited (insert/update) by users. > I should create a file (in whatever format) containing > changes of the last day,every day.What I have currently > is : > I have two tables of the same structer ,every day i : > > select * from newtable > except > select * from oldtable > > And then format the data with perl script.After that ,i > am copying the data from the new table to the old table .It > works pretty good except that the copying is VERY slow: > I use : > insert into old from (select * from newtable); > > Is there any more afficient way to do what i need , > that somebody already use? > Is there any more afficient way to copy data between > tables? > I know that i can use COPY to copy files from > and to files ,can it be used here? > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > If I had to do this, I would use triggers which, on every INSERT/UPDATE by users, would insert the new item + timestamp or date in a new table. I hope this will help you.
On Fri, 27 Feb 2004 20:14:53 +0100, Jean-Michel Chabanne <jeanmichel.chabanne@online.fr> wrote: > Le ven 27/02/2004 ׳� 03:40, Tsirkin Evgeny a ׳™crit : >> Hi all! >> I have the following task : >> I have some tables edited (insert/update) by users. >> I should create a file (in whatever format) containing >> changes of the last day,every day.What I have currently >> is : >> I have two tables of the same structer ,every day i : >> >> select * from newtable >> except >> select * from oldtable >> >> And then format the data with perl script.After that ,i >> am copying the data from the new table to the old table .It >> works pretty good except that the copying is VERY slow: >> I use : >> insert into old from (select * from newtable); >> >> Is there any more afficient way to do what i need , >> that somebody already use? >> Is there any more afficient way to copy data between >> tables? >> I know that i can use COPY to copy files from >> and to files ,can it be used here? >> >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 5: Have you checked our extensive FAQ? >> >> http://www.postgresql.org/docs/faqs/FAQ.html >> > > If I had to do this, I would use triggers which, on every INSERT/UPDATE > by users, would insert the new item + timestamp or date in a new table. > > I hope this will help you. > > That would not make me able to know WHAT was the change: what was there befor and after the update
Tsirkin Evgeny wrote: > On Fri, 27 Feb 2004 20:14:53 +0100, Jean-Michel Chabanne > <jeanmichel.chabanne@online.fr> wrote: > >> Le ven 27/02/2004 ׳� 03:40, Tsirkin Evgeny a ׳™crit : >>> Hi all! >>> I have the following task : >>> I have some tables edited (insert/update) by users. >>> I should create a file (in whatever format) containing >>> changes of the last day,every day.What I have currently >>> is : >>> I have two tables of the same structer ,every day i : >>> >>> select * from newtable >>> except >>> select * from oldtable >>> >>> And then format the data with perl script.After that ,i >>> am copying the data from the new table to the old table .It >>> works pretty good except that the copying is VERY slow: >>> I use : >>> insert into old from (select * from newtable); >>> >>> Is there any more afficient way to do what i need , >>> that somebody already use? >>> Is there any more afficient way to copy data between >>> tables? >>> I know that i can use COPY to copy files from >>> and to files ,can it be used here? >>> >>> >>> ---------------------------(end of broadcast)--------------------------- >>> TIP 5: Have you checked our extensive FAQ? >>> >>> http://www.postgresql.org/docs/faqs/FAQ.html >>> >> >> If I had to do this, I would use triggers which, on every INSERT/UPDATE >> by users, would insert the new item + timestamp or date in a new table. >> >> I hope this will help you. >> >> > That would not make me able to know WHAT was the change: > what was there befor and after the update > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend The trigger must write into two tables of course, the original table and a new table which is a kind of log table. Lest's say users insert all data into a table T1. The trigger inserts data into T1 _AND_ a copy of the item, with timestamp or date, into T2. To know what was changed a day, you only have to query the table T2. This would work fine, if I've understood what you want to do, of course.