Re: Query that produces index information for a Table

I have been using PostgreSQL for a short while, but I have not had to
use the pg_catalog tables before and the columns are a little cryptic to
me. I think it ties to pg_class, but I am not sure how to relate them.
Also, I have not had a chance to us the \dt function. Can you give me
some pointers or point me to the docs?

Thanks,

Craig Boyd

Tom Lane wrote:
> "Boyd, Craig" <craig(at)mysoftforge(dot)com> writes:
>
>> I am trying to pull together some general information about indices
>> (indexes?) for a particular table.
>> I need the following: Index Name, Table Name, Column Name,
>> Unique/Non-Unique, and ordinal position in the index.
>> The information_schema.key_column_usage gets me most of the way there, I
>> think, but does not tell me whether the index is unique and does not
>> seem to differentiate between indices and other types of constraints.
>>
>
> The information_schema gets you *none* of the way there, actually,
> because it's a creature of the SQL standard and indexes are outside the
> standard (yes, really). You can find out about unique constraints from
> the information_schema views, but not about non-unique indexes, nor
> even indexes that are unique but weren't created via unique-constraint
> syntax.
>
> If you want to know about all indexes, you'll need to get your hands
> dirty with looking at the PG system catalogs. I'd suggest looking at
> the queries psql generates for \dt (use psql -E to watch these) and
> then modifying them to suit your purposes.
>
> regards, tom lane
>
>
>

--
I am using the free version of SPAMfighter.
We are a community of 7 million users fighting spam.
SPAMfighter has removed 1388 of my spam emails to date.
Get the free SPAMfighter here: http://www.spamfighter.com/len

The Professional version does not have this message