Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id E90819FA2B9 for ; Tue, 7 Nov 2006 20:55:29 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 76056-04 for ; Tue, 7 Nov 2006 20:55:27 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from nf-out-0910.google.com (nf-out-0910.google.com [64.233.182.184]) by postgresql.org (Postfix) with ESMTP id B1A239FA298 for ; Tue, 7 Nov 2006 20:55:26 -0400 (AST) Received: by nf-out-0910.google.com with SMTP id x4so337795nfb for ; Tue, 07 Nov 2006 16:55:24 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=Xk6tU0N92owRweBeajIDxMPOi2uU+2gQ9OL/AQGoFX8qsiiwgGtDZ/kdlpG0EOsEnK274eHctZJOXGTeNsGo8icKojguunIQRmiCtwjptibgZifLn6GqsltHNm1k+B3uZzH9JMI359wXOuv5dEsyELYcI1TYsN9Im1MbrmEjqeA= Received: by 10.82.129.5 with SMTP id b5mr1620778bud.1162947324178; Tue, 07 Nov 2006 16:55:24 -0800 (PST) Received: by 10.82.173.15 with HTTP; Tue, 7 Nov 2006 16:55:24 -0800 (PST) Message-ID: <1c66bda80611071655p45c02786ya8a7ea38a6590a84@mail.gmail.com> Date: Wed, 8 Nov 2006 13:55:24 +1300 From: adey To: "Mark Steben" Subject: Re: Question Cc: jeff@frostconsultingllc.com, pgsql-admin@postgresql.org In-Reply-To: <001001c702ae$5989da00$b501a8c0@D1RPQZB1> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_13657_1085672.1162947324144" References: <001001c702ae$5989da00$b501a8c0@D1RPQZB1> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200611/64 X-Sequence-Number: 23591 ------=_Part_13657_1085672.1162947324144 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline I think the following query should list clustered indexes for you:- select * from pg_index where indisclustered = 't' On 11/8/06, Mark Steben wrote: > > Thank you, Jeff. That worked like a champ. My initial problem > Had to do with not understanding the concept of OID datatypes. > My next learning task is to read up on OID. > > Thanks again, Mark > > -----Original Message----- > From: jeff@frostconsultingllc.com [mailto:jeff@frostconsultingllc.com] > Sent: Tuesday, November 07, 2006 3:29 PM > To: Mark Steben > Cc: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] Question > > On Tue, 7 Nov 2006, Mark Steben wrote: > > > I am very new to PostgreSQL. Is it appropriate to pose questions to > this > > email list? > > It might be better on pgsql-sql, but I'm not sure. > > > I am trying to come up with a query that will list the names of the > database > > indexes that > > > > Have been chosen as clustering indexes. I know I can get the INDEXRELID > > from PG.INDEX > > > > But have yet to figure out how to get the index name from there. Any > help > > would be appreciated. > > > > And, if this is not an appropriate forum to ask questions please tell > me. > > You want to join on pg_class.oid. You can see the info on pg_index here > in > the docs: http://www.postgresql.org/docs/8.1/static/catalog-pg-index.html > > select i.indexrelid,c.relname from pg_index i, pg_class c where > i.indexrelid > = > c.oid; > > will likely give you what you're after. > > -- > Jeff Frost, Owner > Frost Consulting, LLC http://www.frostconsultingllc.com/ > Phone: 650-780-7908 FAX: 650-649-1954 > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > ------=_Part_13657_1085672.1162947324144 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
I think the following query should list clustered indexes for you:-
 
select * from pg_index
where indisclustered = 't'

 
On 11/8/06, Mark Steben <msteben@autorevenue.com> wrote:
Thank you, Jeff.  That worked like a champ.  My initial problem
Had to do with not understanding the concept of OID datatypes.
My next learning task is to read up on OID.

Thanks again, Mark

-----Original Message-----
From: jeff@frostconsultingllc.com [mailto: jeff@frostconsultingllc.com]
Sent: Tuesday, November 07, 2006 3:29 PM
To: Mark Steben
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Question

On Tue, 7 Nov 2006, Mark Steben wrote:

> I am very new to PostgreSQL.  Is it appropriate to pose questions to this
> email list?

It might be better on pgsql-sql, but I'm not sure.

> I am trying to come up with a query that will list the names of the
database
> indexes that
>
> Have been chosen as clustering indexes.  I know I can get the INDEXRELID
> from PG.INDEX
>
> But have yet to figure out how to get the index name from there.  Any help
> would be appreciated.
>
> And, if this is not an appropriate forum to ask questions please tell me.

You want to join on pg_class.oid.  You can see the info on pg_index here in
the docs: http://www.postgresql.org/docs/8.1/static/catalog-pg-index.html

select i.indexrelid,c.relname from pg_index i, pg_class c where i.indexrelid
=
c.oid;

will likely give you what you're after.

--
Jeff Frost, Owner       <jeff@frostconsultingllc.com>
Frost Consulting, LLC   http://www.frostconsultingllc.com/
Phone: 650-780-7908     FAX: 650-649-1954


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

------=_Part_13657_1085672.1162947324144--