2

How can I move data in similar tables (same number of columns, data types. If they are not same, it can be achieved with a view I hope) most efficiently between schemas of the same postgresql database?

EDIT

Sorry for the vagueness. I intend to use the additional schemas as archives for data not often needed (to improve performance). To be more precise data older than 2 years is to be archived. It is okay to take the server offline, but by not more than a day, at most 2. It is an accounting software for a medium sized company. By liberal estimates the number of records in an year wont go near a million.

Improve this question
6
  • do you mean insert into local.tablex as select * from remote.tablex? Commented Apr 17, 2013 at 20:22
  • you need to specify if it's OK to put the DB offline or not Commented Apr 17, 2013 at 20:24
  • 1
    This is very vague. Provide a concrete test case. The best practice depends on the details. Commented Apr 17, 2013 at 21:06
  • @Erwin. I am really sorry for the vagueness. I have updated the question. Please tell me if any more details could be useful. I would also like to hear your replies for the question. Thanks Commented Apr 18, 2013 at 1:52
  • @Randy I am sorry for the vagueness. I have updated the question. Both schemas reside on the same database, on the same server. They just need to be moved, from one to another. So both are local, but reside in their own schemas Commented Apr 18, 2013 at 1:57

1 Answer 1

Reset to default
4 
insert into target_schema.table_one (col1, col2, col3)
select col1, col2, col3
from source_schema.other_table
where <some condition to select the data to be moved>;

If you really want to "move" the data (i.e. delete the rows from the source table), you need to can use

If the table is the target of a foreign key you cannot use truncate in that case you need to use 

delete from source_schema.other_table
where <some condition to select the data to be moved>;

You can combine both steps into a single statement, if you want to:

with deleted_data as (
   delete from source_schema.other_table
   where <some condition to select the data to be moved>;
   returning *
)
insert into target_schema.table_one (col1, col2, col3)
select col1, col2, col3
from deleted_data;
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks for the answer. But I was a little vague on my part. I have updated the question. The tables with old data cannot be truncated. They will contain some information. Only some of it is to be moved to the archive. Also would it not be better to drop indexes (in the tables to be inserted), do a SELECT, pipe the ouput to COPY and recreate the indexes.
@a_horse_with_no_name just answered the question resolving main problem. Now, specify 'old data' and 'some information' and put in into where statement to delete from. You can also do a several statements for several terms.
@Richard: you can use a condition in the where clause to only copy part of the data.
@a_horse_with_no_name thanks for the inputs. what do you think of the method i mentioned above (in my first comment to you?) the problem here is efficiency. because i will be moving a lot of data at once. so i want the most efficient way to tackle it.
@Richard: If everything is in the same database, using copy will be slower than an insert ... select. If you are moving a lot of data, dropping and re-creating the indexes on the source and the target can probably make the process more efficient.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.