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

Re: Moving data from one set of tables to another?

From: Carol Walter <walterc(at)indiana(dot)edu>
To: howard(at)yankeescientific(dot)com
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Moving data from one set of tables to another?
Date: 2008-09-18 20:52:02
Message-ID: E6BCB859-CF05-40B8-B283-2097492BC5E0@indiana.edu (view raw or flat)
Thread:
Lists: pgsql-novice
What do you want for your end product?  Are the old tables empty  
after you put the data into the new tables?

Carol

On Sep 18, 2008, at 3:02 PM, Howard Eglowstein wrote:

> 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
>
> -- 
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice


In response to

Responses

pgsql-novice by date

Next:From: Howard EglowsteinDate: 2008-09-18 22:39:03
Subject: Re: Moving data from one set of tables to another?
Previous:From: Howard EglowsteinDate: 2008-09-18 19:02:26
Subject: Moving data from one set of tables to another?

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