I am running PostgreSQL 9.3.4 on Windows. For many of the tables in my database, I need to create another version of the tables, in another schema, with fewer records (as determined by a simple condition).
For example, say I have a public.orders table with 15 million records. I want to create a myschema.orders table with only the orders placed after 1-Jan-2010. This condition reduces the table from 15 to 1.5 million records.
My problem: I want myschema.orders to have the same constraints and indices as public.orders, but I'd like to add them after populating the table as this should be faster. I'd rather not use a view because it would be much slower. I can do:
create table myschema.orders (like public.orders including all);
insert into myschema.orders select * public.orders where MyCondition = True
The first line copies all constraints (excluding foreign keys) and indices before populating the table. The code works, but is about 15 times slower than inserting the same records into a table with no constraints.
What is the easiest way to populate the table first, and then copying the constraints? Or can I maybe disable and then re-enable the constraints? I need to do this for about 30 tables, each of which with about 10 constraints and 7 indices, so I'd like to automate the process as much as possible.
Thank you!