copying data between tables

[Tsirkin Evgeny]

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

[Jean-Michel Chabanne]

Post by Tsirkin Evgeny
Hi all!
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
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
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.



---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tsirkin Evgeny]

On Fri, 27 Feb 2004 20:14:53 +0100, Jean-Michel Chabanne

Post by Jean-Michel Chabanne

Post by Tsirkin Evgeny
Hi all!
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
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
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

[Jean-Michel Chabanne]

Post by Tsirkin Evgeny
On Fri, 27 Feb 2004 20:14:53 +0100, Jean-Michel Chabanne

Post by Jean-Michel Chabanne

Post by Tsirkin Evgeny
Hi all!
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
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
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.

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.