Re: how to find index columns

On 2006-12-06, Timasmith <timasmith(at)hotmail(dot)com> wrote:
>
> Andrew - Supernews wrote:
>> You seem to be mistaking the information_schema for something which
>> provides complete metadata - it does not, and due to the restrictions of
>> the SQL spec which defines it, it never can be complete.
>
> never is a long time. Never ever? Never ever can any database
> administrator every know which columns are indexed by the database?

The database administrator can find out any time he likes by querying
the PG system catalogs (or views based on them, as the newsysviews ones
are). No need to parse the definition string.

"Why isn't there a view to show indexed columns" is a different question
to "why isn't there a view in information_schema to show indexed columns".

The answer to the former is "because after I did most of the work to
provide a complete set of system views, many developers expressed the
view that they would be pointless, and consequently I became
insufficiently motivated to finish them beyond what I myself use".

The answer to the latter is "because the definition of information_schema
is in the SQL spec, and it doesn't include indexes".

> But I still love it, I will do a parsing script for now.

http://pgfoundry.org/projects/newsysviews/ and look for index_columns.sql
in the CVS.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services