How can I create an index in Postgres dynamically over many tables but always with different names.

Lets assume table x has a column y with all table names of hundreds tables which need to get an index on their z column type numeric. How can I run a loop which dynamically creates with the table names of column y of table x indexes on every table?

  • Obfuscating names with x, y and z doesn't really help in understanding the problem.
    – user1822
    Commented Aug 12, 2019 at 13:41

1 Answer 1


You can use dynamic SQL with a do statement that iterates over the tables in a schema:

 l_statement text;
 l_rec record;
  for l_rec in select y 
               from x
    l_statement := format('create index on %I (z)', l_rec.y);
    execute l_statement;
  end loop;

Postgres will automatically generate a name for the new index.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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