Обсуждение: extended index info

Hi all,

I'm trying to work out which fields an index relates to.

If I look at an index:

\di+ news_pkey

  Schema | Name      | Type  | Owner | Description | Table
--------+-----------+-------+-------+-------------+--------
  public | news_pkey | index | chris |             | news


It doesn't show me which fields it actually applies to, only the table.
I'm sure there is a way to include which fields (whether it comes back
as an array or as multiple lines in the output I don't really care), but
I don't know enough about the pg_* tables to know where to start :)

I can see the create definition in pg_indexes but I'm after just the
fields that it applies to.

Any suggestions?

Thanks!

--
Postgresql & php tutorials
http://www.designmagick.com/

On Fri, Mar 03, 2006 at 05:46:59PM +1100, Chris wrote:
> If I look at an index:
>
> \di+ news_pkey
>
>  Schema | Name      | Type  | Owner | Description | Table
> --------+-----------+-------+-------+-------------+--------
>  public | news_pkey | index | chris |             | news
>
>
> It doesn't show me which fields it actually applies to, only the table.

\d news_pkey

--
Michael Fuhr

Michael Fuhr wrote:
> On Fri, Mar 03, 2006 at 05:46:59PM +1100, Chris wrote:
>
>>If I look at an index:
>>
>>\di+ news_pkey
>>
>> Schema | Name      | Type  | Owner | Description | Table
>>--------+-----------+-------+-------+-------------+--------
>> public | news_pkey | index | chris |             | news
>>
>>
>>It doesn't show me which fields it actually applies to, only the table.
>
>
> \d news_pkey
>

Derr.

Thanks :)


--
Postgresql & php tutorials
http://www.designmagick.com/

Michael Fuhr <mike@fuhr.org> writes:
> On Fri, Mar 03, 2006 at 05:46:59PM +1100, Chris wrote:
>> If I look at an index:
>> It doesn't show me which fields it actually applies to, only the table.

> \d news_pkey

Also, \d on the index's parent table will show you all the index
definitions.  This is more useful than the "\d index" display in some
cases, particularly non-default opclasses and index expressions.
For example:

regression=# create index fooi on tenk1((unique1+unique2));
CREATE INDEX
regression=# \d fooi
    Index "public.fooi"
     Column      |  Type
-----------------+---------
 pg_expression_1 | integer
btree, for table "public.tenk1"

regression=# \d tenk1
...
Indexes:
    "fooi" btree ((unique1 + unique2))
...

I'm not really sure why we don't account for these cases in "\d index",
unless that it's hard to see where to fit the info into a tabular
layout.

            regards, tom lane