list tables and their indexes

From: "Johnson, Shaunn" <SJohnson6(at)bcbsm(dot)com>
To: "pgsql (E-mail)" <pgsql-general(at)postgresql(dot)org>
Subject: list tables and their indexes
Date: 2003-07-09 19:18:47
Message-ID: 73309C2FDD95D11192E60008C7B1D5BB05FED563@snt452.corp.bcbsm.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Howdy:

Is there a way (SQL query) to get a list of
tables and their indexes (rather, what the
\di <index_name> brings back)?

I am doing the 'psql -U <name> -d <database> -E
to figure out how the description of \d <table>
give the 'Indexes:' information at the bottom of
the page, but I can't seem to figure out
how it's pulling that data back (aside from
querying against pg_class and pg_index and
why it is going there).

Just to make myself clear - I am not talking
about the following:

[snip]
SELECT c2.relname
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 't_table'
AND c.oid = i.indrelid AND i.indexrelid = c2.oid
AND NOT i.indisunique ORDER BY c2.relname
[/snip]

But something that I can put into a script to
to identify the table and bring back this:

[snip from \di <index_name>]
Index "t_table_2003_idx"
Column | Type
-----------+---------
person_id | integer
btree

[/snip from \di <index_name>}

Thanks in advance.

-X

Browse pgsql-general by date

  From Date Subject
Next Message Albert Cervera Areny 2003-07-09 19:44:36 INSERT but no UPDATE or DELETE from windows
Previous Message Jan Wieck 2003-07-09 19:11:12 Re: ERROR: Relation "pg_user" does not exist