Quantcast

Get index information from information_schema?

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Get index information from information_schema?

AlannY
Hello, there.

I need a method of extracting information about indexes of any table
from information_schema.

Have you any suggestions?

Thank you.

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Get index information from information_schema?

Albe Laurenz *EXTERN*
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

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Get index information from information_schema?

Dann Corbit
> -----Original Message-----
> From: [hidden email] [mailto:pgsql-general-
> [hidden email]] On Behalf Of Albe Laurenz
> Sent: Tuesday, March 18, 2008 1:24 AM
> To: AlannY *EXTERN*; [hidden email]
> 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?

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Get index information from information_schema?

Erik Jones

On Mar 18, 2008, at 1:28 PM, Dann Corbit wrote:

>> -----Original Message-----
>> From: [hidden email] [mailto:pgsql-general-
>> [hidden email]] On Behalf Of Albe Laurenz
>> Sent: Tuesday, March 18, 2008 1:24 AM
>> To: AlannY *EXTERN*; [hidden email]
>> 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®
[hidden email]
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




--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Get index information from information_schema?

Dann Corbit
> -----Original Message-----
> From: Erik Jones [mailto:[hidden email]]
> Sent: Tuesday, March 18, 2008 11:51 AM
> To: Dann Corbit
> Cc: Albe Laurenz; AlannY *EXTERN*; [hidden email]
> Subject: Re: [GENERAL] Get index information from information_schema?
>
>
> On Mar 18, 2008, at 1:28 PM, Dann Corbit wrote:
>
> >> -----Original Message-----
> >> From: [hidden email] [mailto:pgsql-general-
> >> [hidden email]] On Behalf Of Albe Laurenz
> >> Sent: Tuesday, March 18, 2008 1:24 AM
> >> To: AlannY *EXTERN*; [hidden email]
> >> 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.

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Get index information from information_schema?

Erik Jones

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®
[hidden email]
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




--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Get index information from information_schema?

Albe Laurenz *EXTERN*
In reply to this post by Dann Corbit
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

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Loading...