How to know the indexes on a Table

"Anirban Pal" <anirban.pal@newgen.co.in> writes:

Is there any way, to know the name of indexes on a table, defined in a database. I mean can I query something like
Select Index_name from pg_class where relname = "Table_name" . Thanks in advance.

You need to join through pg_index, specifically its columns indrelid and
indexrelid. Try running psql with the -E option and look at the queries
it uses to implement "\d Table_name".

regards, tom lane

Hi all,

Is there any way, to know the name of indexes on a table, defined in
a database. I mean can I query something like
Select Index_name from pg_class where relname = "Table_name" .
Thanks in advance.

---  Thanks & Reagrds  ----
Anirban Pal  |  Software Engineer
Newgen Software Technologies Ltd.
Contact: (011) 26815467-72 | Extn: 177

Disclaimer :- This e-mail and any attachment may contain
confidential, proprietary or legally privileged information. If you
are not the origin al intended recipient and have erroneously
received this message, you are prohibited from using, copying,
altering or disclosing the content of this message. Please delete it
immediately and notify the sender. Newgen Software Technologies Ltd
(NSTL) accepts no responsibilities for los s or damage arising from
the use of the information transmitted by this email including
damages from virus and further acknowledges that no bin ding nature
of the message shall be implied or assumed unless the sender does so
expressly with due authority of NSTL.

Just take a look at the system catalogs, you'll find the view pg_index
as well http://www.postgresql.org/docs/8.3/static/catalog-pg-index.html

Example:
SELECT
nmsp.nspname AS schemaname,
tcls.relname AS tablename,
icls.relname AS indexname
FROM
pg_index
JOIN pg_class AS icls ON pg_index.indexrelid = icls.oid
JOIN pg_class AS tcls ON pg_index.indrelid = tcls.oid
JOIN pg_namespace AS nmsp ON tcls.relnamespace = nmsp.oid
WHERE
nmsp.nspname NOT IN ('pg_catalog', 'pg_toast')
ORDER BY
schemaname ASC,
tablename ASC,
indexname ASC;

Kind regards,
Frank