Thread: Query that produces index information for a Table

Hello All,

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.
I can't believe I am the first to ask this question so I am hoping
someone can send me what they use to get this information.

Thanks,

Craigbert

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

The Professional version does not have this message

"Boyd, Craig" <craig@mysoftforge.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 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@mysoftforge.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

On 10 May 2010, at 6:02, Boyd, Craig wrote:

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

Why didn't you just try it?

dalroi:solfertje > psql -E development
Welcome to psql 8.3.9, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

development=> \dt
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index
' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
  r.rolname as "Owner"
FROM pg_catalog.pg_class c
     JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
  AND n.nspname <> 'pg_catalog'
  AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

No relations found.

> SPAMfighter has removed 1388 of my spam emails to date.

You shouldn't be sending spam, you know ;) Or isn't that what's meant here?
That's a pretty useless number anyhow, the spam filter I use (dspam) catches about that much in a weeks time for my
accountalone. Without a total or a starting date its just a meaningless number. 

Do you have any influence on what it prints under your messages?

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4be7da5510411734319221!