Обсуждение: Extracting Index Creation Script

Hello,

Does anyone have any good code to extract the metadata needed to create
indexes on a specific table?  The Client Tools (like pgadmin-III) presents
that code so I'm sure it's extractable but could not find it in my trace
that I ran while operating pgadmin...

Thanks in advance,

Paul

--
View this message in context: http://www.nabble.com/Extracting-Index-Creation-Script-tf4680848.html#a13375314
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Hello,

I answered my own question. :)  I continued looking last night after I
posted this and found a really easy way to get this info...

select * from pg_indexes
where tablename like 'YOURTABLENAME'

This will give you the DDL to create your indexes.

Regards,

Paul





Paul Silveira wrote:
>
> Hello,
>
> Does anyone have any good code to extract the metadata needed to create
> indexes on a specific table?  The Client Tools (like pgadmin-III) presents
> that code so I'm sure it's extractable but could not find it in my trace
> that I ran while operating pgadmin...
>
> Thanks in advance,
>
> Paul
>
>

--
View this message in context: http://www.nabble.com/Extracting-Index-Creation-Script-tf4680848.html#a13384579
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

If you know the index name then:
SELECT pg_get_indexdef('your_index_name'::regclass)
will do.

In case you want a full list of indices for a certain table:
SELECT c2.relname, pg_get_indexdef(i.indexrelid, 0, true), c2.reltablespace
  FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
 WHERE c.oid = 'your_table_name'::regclass AND c.oid = i.indrelid AND i.indexrelid = c2.oid

If you have more questions of that kind try starting psql with -E option which enables internal queries' display (this is what I did).