Skip site navigation (1) Skip section navigation (2)

Moving data from one set of tables to another?

From: Howard Eglowstein <howard(at)yankeescientific(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Moving data from one set of tables to another?
Date: 2008-09-18 19:02:26
Message-ID: 48D2A5C2.50904@yankeescientific.com (view raw or flat)
Thread:
Lists: pgsql-novice
I have three tables called 'data_a', 'data_b' and 'data_c' which each 
have 50 columns. One of the columns in each is 'id' and is used to keep 
track of which data in data_b and data_c corresponds to a row in 
data_a.  If I want to get all of the data in all 150 fields for this 
month (for example), I can get it with:

select * from (data_a, data_b, data_c) where data_a.id=data_b.id AND 
data_a.id = data_c.id AND timestamp >= '2008-09-01 00:00:00' and 
timestamp <= '2008-09-30 23:59:59'

What I need to do is execute this search which might return several 
thousand rows and write the same structure into 'new_a', 'new_b' and 
'new_c'. What i'm doing now in a C program is executing the search 
above. Then I execute:

INSERT INTO data_a (timestamp, field1, field2 ...[imagine 50 of them]) 
VALUES ('2008-09-01 00:00:00', 'ABC', 'DEF', ...);
Get the ID that was assigned to this row since 'id' is a serial field 
and the number is assigned sequentially. Say it comes back as '1'.
INSERT INTO data_b (id, field1, field2 ...[imagine 50 of them]) VALUES 
('1', 'ABC', 'DEF', ...);
INSERT INTO data_c (id, field1, field2 ...[imagine 50 of them]) VALUES 
('1', 'ABC', 'DEF', ...);

That moves a copy of the three rows of data form the three tables into 
the three separate new tables.
 From the original group of tables, the id for these rows was, let's 
say, '1234'. Then I execute:

DELETE FROM data_a where id='1234';
DELETE FROM data_b where id='1234';
DELETE FROM data_c where id='1234';

That deletes the old data.

This works fine and gives me exactly what I wanted, but is there a 
better way? This is 7 SQL calls and it takes about 3 seconds per moved 
record on our Linux box.

Any thoughts or suggestions would be appreciated.

Thanks,

Howard

Responses

pgsql-novice by date

Next:From: Carol WalterDate: 2008-09-18 20:52:02
Subject: Re: Moving data from one set of tables to another?
Previous:From: ries van TwiskDate: 2008-09-18 16:06:01
Subject: Re: cpu utilization question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group