Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group