Data from table to table

[gpicazo]

Hello everyone,

I have two tables in the same database and same schema. I am writing a program that uses this database in c/c++ using the libpq library. What I need to do now is move SOME data from the first table to the second. By move, I mean that I want the data inserted into the second table and removed from the first (as opposed to just copying it).

My questions are:
1) Is there a faster, better way of doing it other than:
a) insert into table2 select * from table1 where ...
b) delete from table1 where ...
? And if so, how would you do it?

2) Would it be faster to do data moving by executing each command from the c++ code, or would it be faster to create a postgresql procedure or function to do it, and then call it from the c++ code? If it's faster to create a postgresql procedure/function, could you provide sample code of how to do this?

Thanks in advance,
Genaro

 

[gwinn7]

Why would you want to avoid the copy and then delete steps you mentioned?

It seems that using the database platform natively to copy the data would be the fastest most efficient route. Other than that, I don't know of any other faster way.

Gary
gwinn7

 

[feherke]

Hi

1) Is there a faster, better way of doing it other than:
a) insert into table2 select * from table1 where ...
b) delete from table1 where ...
? And if so, how would you do it?

I had problem with such moving some time ago. The problem was that the [tt]delete[/tt] was slowed down by the sub-[tt]select[/tt]. My first code was something like this :

[b]insert into[/b] list_old [b]select[/b] * [b]from[/b] list [b]where[/b] status='done';

[b]delete from[/b] list [b]where[/b] id [b]in[/b] ([b]select[/b] o.id [b]from[/b] list_old o [b]inner join[/b] list [b]using[/b] (id));

Fortunately the [tt]update[/tt] statement has somethint which the [tt]delete[/tt] lacks : the [tt]from[/tt] clause. So using an [tt]update[/tt] to avoid using a sub-[tt]select[/tt], increased the speed over 100 times.

[b]insert into[/b] list_old [b]select[/b] * [b]from[/b] list [b]where[/b] status='done';

[b]update[/b] list [b]set[/b] id=-id [b]from[/b] list_old o [b]where[/b] o.id=list.id;

[b]delete from[/b] list [b]where[/b] id<0;

Feherke.

http://rootshell.be/~feherke/

 

[gwinn7]

Feherke,

I can only think of 2 other options than a subselect...

Method 1

' As before...
insert into list_old select * from list where status = 'done';

' Then...
delete from list where status = 'done';

Method 2 Now, I see the potential problem with method 1, but what about this method? I am not sure if this will beat a subselect on performance, but you could try it...

' append new column...
alter table list add column removeyn bool;
alter table list create index remove_idx on list.removeyn;

' update
update list set removeyn = 't' where status = 'done';

' as before except with flag...
insert into list_old select * from list where removeyn = 't';

' finally..
delete from list where removeyn = 't';

What do you think?

Gary
gwinn7

 

[feherke]

Hi

delete from list where status = 'done';

That status='done' condition was for this example only. The actual [tt]select[/tt] for the movable records was much more complex. So no, such [tt]delete[/tt] would not be possible.

Regarding your second suggestion, is ok. But I prefered to use the sign of id to mark deletable records. The id field was already indexed.

But I find interesting your idea of puting the [tt]update[/tt] before [tt]insert[/tt]. I will think to it and probably implementing it. Thanks.

Feherke.

http://rootshell.be/~feherke/