> -----Original Message----- > From: Erik Jones [mailto:erik@xxxxxxxxxx] > Sent: Tuesday, March 18, 2008 11:51 AM > To: Dann Corbit > Cc: Albe Laurenz; AlannY *EXTERN*; pgsql-general@xxxxxxxxxxxxxx > Subject: Re: Get index information from information_schema? > > > On Mar 18, 2008, at 1:28 PM, Dann Corbit wrote: > > >> -----Original Message----- > >> From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > >> owner@xxxxxxxxxxxxxx] On Behalf Of Albe Laurenz > >> Sent: Tuesday, March 18, 2008 1:24 AM > >> To: AlannY *EXTERN*; pgsql-general@xxxxxxxxxxxxxx > >> Subject: Re: 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 (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
