X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id D88C7D1E132 for ; Sun, 29 Feb 2004 20:20:13 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 76523-02 for ; Sun, 29 Feb 2004 16:20:13 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 807F6D1B83D for ; Sun, 29 Feb 2004 16:20:11 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i1TKKA1Y077648 for ; Sun, 29 Feb 2004 20:20:10 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i1TK9uR3076341 for pgsql-admin@postgresql.org; Sun, 29 Feb 2004 20:09:56 GMT From: Jean-Michel Chabanne X-Newsgroups: comp.databases.postgresql.admin Subject: Re: copying data between tables Date: Sun, 29 Feb 2004 21:10:05 +0100 Organization: Hub.Org Networking Services Lines: 63 Message-ID: References: <1077909293.1345.3.camel@yoyo.local> Mime-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 8Bit X-Complaints-To: usenet@news.hub.org User-Agent: KNode/0.7.2 To: pgsql-admin@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/397 X-Sequence-Number: 12570 Tsirkin Evgeny wrote: > On Fri, 27 Feb 2004 20:14:53 +0100, Jean-Michel Chabanne > 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.