Thread: Getting the indexes of a table

Hello,

I want to get the indexes that are installed in one table.
I looked at the information_schema but there was nothing to use.

I am looking for something like

SELECT column_name, index_name FROM ... WHERE table_name = 'abc'

How should my query look?

Thank you,

Christian

Christian Hofmann <christian.hofmann@gmx.de> schrieb:

> Hello,
>
> I want to get the indexes that are installed in one table.
> I looked at the information_schema but there was nothing to use.

Start psql with -E and type '\d <your_table>. Now you can see how you
can retrieve this informations.


HTH, Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."    (unknow)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Thank you Andreas,

Based on that querys I was possible to build the query I need:

Here is what I got:

SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered,
pg_catalog.pg_get_indexdef(i.indexrelid, 0, true), c2.reltablespace
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
WHERE c.oid = (SELECT c.oid FROM pg_catalog.pg_class c LEFT JOIN
pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname =
'mytablename' AND n.nspname = 'myschemaname') AND c.oid = i.indrelid AND
i.indexrelid = c2.oid
ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname

Christian

> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org] On Behalf Of
> Andreas Kretschmer
> Sent: Thursday, April 27, 2006 7:17 PM
> To: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] Getting the indexes of a table
>
> Christian Hofmann <christian.hofmann@gmx.de> schrieb:
>
> > Hello,
> >
> > I want to get the indexes that are installed in one table.
> > I looked at the information_schema but there was nothing to use.
>
> Start psql with -E and type '\d <your_table>. Now you can see how you
> can retrieve this informations.
>
>
> HTH, Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a
> completely
> unintentional side effect.
> (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly."
>   (unknow)
> Kaufbach, Saxony, Germany, Europe.              N 51.05082°,
> E 13.56889°
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>