Re: Getting the indexes of a table

From: "Christian Hofmann" <christian(dot)hofmann(at)gmx(dot)de>
To: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Getting the indexes of a table
Date: 2006-04-27 17:35:27
Message-ID: 00f901c66a20$f9578cc0$7b00a8c0@taschenrechner
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)postgresql(dot)org
> [mailto:pgsql-novice-owner(at)postgresql(dot)org] On Behalf Of
> Andreas Kretschmer
> Sent: Thursday, April 27, 2006 7:17 PM
> To: pgsql-novice(at)postgresql(dot)org
> Subject: Re: [NOVICE] Getting the indexes of a table
>
> Christian Hofmann <christian(dot)hofmann(at)gmx(dot)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
>
>

In response to

Browse pgsql-novice by date

  From Date Subject
Next Message Bruno Wolff III 2006-04-27 19:48:03 Re: error handling
Previous Message Andreas Kretschmer 2006-04-27 17:17:04 Re: Getting the indexes of a table