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.

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