Thread: copying data between tables

copying data between tables

From
Tsirkin Evgeny
Date:
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?


Re: copying data between tables

From
Jean-Michel Chabanne
Date:
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.



Re: copying data between tables

From
Tsirkin Evgeny
Date:
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




Re: copying data between tables

From
Jean-Michel Chabanne
Date:
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.