Re: Getting the indexes of a table - Mailing list pgsql-novice

From Christian Hofmann
Subject Re: Getting the indexes of a table
Date
Msg-id 00f901c66a20$f9578cc0$7b00a8c0@taschenrechner
Whole thread Raw
In response to Re: Getting the indexes of a table  (Andreas Kretschmer <akretschmer@spamfence.net>)
List pgsql-novice
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
>
>



pgsql-novice by date:

Previous
From: Andreas Kretschmer
Date:
Subject: Re: Getting the indexes of a table
Next
From: Bruno Wolff III
Date:
Subject: Re: error handling