How to batch create index on 1000 tables

Question

I have a parent table called ABC in postgres 9.3 db

and about 1000 tables inherit ABC

ABC_1
ABC_2
...
ABC_1002

since index on parent table doesn't affect child table, I have to create indexes on them separately.

Is there any batch way to automate index creating on these tables ?

Hi! Do you have table per date/user? – Lukasz Szozda Nov 20 '15 at 15:16 — Lukasz Szozda, Nov 20 '15 at 15:16
A loop with some dynamic sql should be useful – Mihai Nov 20 '15 at 15:18 — Mihai, Nov 20 '15 at 15:18

score 4 · Accepted Answer · edited May 23 '17 at 11:44

If your tables are really named as in the question (I mean enumeration "_1", "_2") etc. Then that simple query will generate set of CREATE INDEX for you.

SELECT
    'CREATE INDEX ON ' || quote_ident('ABC_' || sub.num ) || ' (column_name);' 
FROM
    (SELECT num FROM generate_series(1,1000) num) sub;

But honestly I expect that you provide only simplified example, so please check this two answers of mine about using SQL to DDL commands generation (first answer, second answer) and this answer to learn how to get list of child relations from system tables.

How to batch create index on 1000 tables

1 Answers1