Now is Setempber
I have 3 tables , t1,t2,t3
When it comes to October,
I want to create another 3 tables named t1_09,t2_09,t3_09 which stored data from t1,t2,t3
(copy t1,t2,t3 to t1_09,t2_09,t3_09)

And then clear the data in t1,t2,t3

How can I do this? Is it posibble?
Please guide me,thank you

  • Thanks you all,I think I will choose @AndriyM 's method – user2492364 Sep 25 '15 at 1:27

Community Wiki answer generated from comments on the question

Craig Ringer: CREATE TABLE ... AS SELECT ..., CREATE TABLE (LIKE othertable, ...). Typically though, this is a sign of poor database design. If you must do this look into using partitioning via table inheritance instead.

Erwin Brandstetter: Or, if you can live with the known limitations, this would be a classical case of inheritance with a partitioning solution. Go with triggers for lots of single-row inserts or rules for bulk inserts.

jcolebrand♦: Add another column for year/month of entry, and use an index to parse over that data, and add a view that only selects stuff from the current month to keep from having to look at the old data till you need it? I mean, this seems like the most overly complex design I've seen.

Andriy M: Or perhaps you want just three more tables that you would copy the data to at the end of each month. So you'd have t1, t2, t3 for the current month, and t1_archive, t2_archive, t3_archive for all the past months.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.