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. – a_horse_with_no_name Aug 12 at 13:41

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, privacy policy and cookie policy

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