PostgreSQL: how to copy constraints and indexes from another table after filling the table

I am running PostgreSQL 9.3.4 on Windows. For many tables in my database, I need to create a different version of the tables in a different schema with fewer records (as defined by a simple condition).

For example, let's say I have a public.orders table with 15 million records. I want to create a myschema.orders table with only orders placed after Jan 1, 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 indexes as public.orders, but I would like to add them after the table is populated as it should be faster. I would rather not use the view because it will 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 indexes before filling the table. The code works, but is about 15 times slower than inserting the same records into a table without restrictions.

What's the easiest way to fill the table first and then copy the constraints? Or can I disable and then re-enable the restrictions? I need to do this for about 30 tables, each containing about 10 constraints and 7 indexes, so I would like to automate the process as much as possible.

Thank!

+3


source to share





All Articles