CODEWITHSUNDEEP
sqlpostgresql
Snorlax
Snorlax

Reputation: 847

Postgres- Script to create a script to create existing indexes on given tables

I have a database with a lot of tables in it. Some of them have names which start with "mytable_". Those tables have some indexes. Now I need to move those indexes also to different server which has similar tables.

I would like to have a script which will create all those indexes that are in given tables on my local server.

Also I would like to make it this way that if this index is already created it will not crash.

How can it be done in Postgres (pg admin)

Upvotes: 4

Views: 3499

Answers (1)

user330315
user330315

Reputation:

You can use pg_get_indexdef() to extra all the index definitions:

select replace(pg_get_indexdef(format('%I.%I', schemaname, indexname)::regclass), ' INDEX ', ' INDEX IF NOT EXISTS ')||';' as ddl
from pg_indexes
where schemaname = 'public'
  and tablename like 'mytable\_%';

The replace is used to "inject" the IF NOT EXISTS so that the statement won't fail if such an index is already present in the target database.

Just spool the result of that query to a file.

Upvotes: 12

Related Questions