Postgresql – Copy indexes from one table to another

ddldynamic-sqlindexplpgsqlpostgresql

I have a series of ETL jobs in which I create a swap table using CREATE TABLE table1_swap LIKE table1. In order to make the populating of table1_swap faster I do not include the indexes. When I'm finished loading however I need to re-apply those indexes to the newly populated table. These indexes are created outside of the scope of these ETL jobs so I would rather not have to hardcode the CREATE INDEX calls if I don't have to.

Is it possible to "transfer" or "copy" a set of indexes from one table to another?

Best Answer

You can retrieve the complete DDL statement for each index from the Postgres system catalogs.

The following will retrieve all CREATE index statements for a single table:

select pg_get_indexdef(idx.oid)||';'
from pg_index ind
  join pg_class idx on idx.oid = ind.indexrelid
  join pg_class tbl on tbl.oid = ind.indrelid
  left join pg_namespace ns on ns.oid = tbl.relnamespace
where tbl.relname = 'your_table_name'
   and ns.nspname = 'your_table_schema';

You can spool the output into a script and run it after you have copied the data and swapped the table names.

Related Question