From: | "Dann Corbit" <DCorbit(at)connx(dot)com> |
---|---|
To: | "Erik Jones" <erik(at)myemma(dot)com> |
Cc: | "Albe Laurenz" <laurenz(dot)albe(at)wien(dot)gv(dot)at>, "AlannY *EXTERN*" <m(at)alanny(dot)ru>, <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Get index information from information_schema? |
Date: | 2008-03-18 22:42:09 |
Message-ID: | D425483C2C5C9F49B5B7A41F8944154701000D16@postal.corporate.connx.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> -----Original Message-----
> From: Erik Jones [mailto:erik(at)myemma(dot)com]
> Sent: Tuesday, March 18, 2008 11:51 AM
> To: Dann Corbit
> Cc: Albe Laurenz; AlannY *EXTERN*; pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] Get index information from information_schema?
>
>
> On Mar 18, 2008, at 1:28 PM, Dann Corbit wrote:
>
> >> -----Original Message-----
> >> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> >> owner(at)postgresql(dot)org] On Behalf Of Albe Laurenz
> >> Sent: Tuesday, March 18, 2008 1:24 AM
> >> To: AlannY *EXTERN*; pgsql-general(at)postgresql(dot)org
> >> Subject: Re: [GENERAL] Get index information from
information_schema?
> >>
> >> AlannY wrote:
> >>> I need a method of extracting information about indexes of any
table
> >>> from information_schema.
> >>>
> >>> Have you any suggestions?
> >>
> >> I am afraid that indexes are not covered by information_schema.
> >>
> >> You'd have to dig into pg_catalog.pg_index for this.
> >
> > Doesn't the PostgreSQL schema have the
> > INFORMATION_SCHEMA.KEY_COLUMN_USAGE view?
> >
> > Or (conversely) are indexes not stored as constraints?
>
> Only if they are PRIMARY KEY or UNIQUE indexes.
It looks like Foreign keys are included as well, because I get the
correct results for this query:
create table t1 (c1 int not null, c2 int not null, c3 char(5), c4 int,
c5 int, constraint pk_t1 PRIMARY KEY (c5,c4));
create table t2 (tc1 int not null, c1 int not null, c2 int not null, c5
char(5), constraint fk_t2 FOREIGN KEY (c1,c2) references t1 (c5,c4));
select CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION from
information_schema.key_column_usage where TABLE_NAME in ('t1', 't2');
So the only thing that will be missing are the indexes that are none of
the above.
It's a shame that there really is no information schema for indexes in
that category.
From | Date | Subject | |
---|---|---|---|
Next Message | Erik Jones | 2008-03-18 23:40:55 | Re: Get index information from information_schema? |
Previous Message | Blair Bethwaite | 2008-03-18 22:23:52 | Re: Is autovacuum on? |