Thread: Get index information from information_schema?
Hello, there. I need a method of extracting information about indexes of any table from information_schema. Have you any suggestions? Thank you.
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. Yours, Laurenz Albe
> -----Original Message----- > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- > owner@postgresql.org] On Behalf Of Albe Laurenz > Sent: Tuesday, March 18, 2008 1:24 AM > To: AlannY *EXTERN*; pgsql-general@postgresql.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?
On Mar 18, 2008, at 1:28 PM, Dann Corbit wrote: >> -----Original Message----- >> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general- >> owner@postgresql.org] On Behalf Of Albe Laurenz >> Sent: Tuesday, March 18, 2008 1:24 AM >> To: AlannY *EXTERN*; pgsql-general@postgresql.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. Erik Jones DBA | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
> -----Original Message-----
> From: Erik Jones [mailto:erik@myemma.com]
> Sent: Tuesday, March 18, 2008 11:51 AM
> To: Dann Corbit
> Cc: Albe Laurenz; AlannY *EXTERN*; pgsql-general@postgresql.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@postgresql.org [mailto:pgsql-general-
> >> owner@postgresql.org] On Behalf Of Albe Laurenz
> >> Sent: Tuesday, March 18, 2008 1:24 AM
> >> To: AlannY *EXTERN*; pgsql-general@postgresql.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.
On Mar 18, 2008, at 5:42 PM, Dann Corbit wrote:
>> 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.
Careful with that, foreign keys are not at all indexes although it is
common practice to put indexes on columns with them.
Erik Jones
DBA | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
Dann Corbit 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? You will find constraints in information_schema, but not indexes. In general, indexes are no constraints. Some constraints are implemented as indexes (primary key, unique), but that's a different story. Yours, Laurenz Albe