0

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?

1
  • 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

1

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

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

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.