CODEWITHSUNDEEP
databasepostgresqlschema
Zach
Zach

Reputation: 538

efficiently move data between schemas of the same database in postgresql

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.

Upvotes: 2

Views: 3311

Answers (1)

user330315
user330315

Reputation: 

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;

Upvotes: 5

Related Questions