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 | | newsIt 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 | | newsIt 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