Thread: Finding PrimaryKey columns of a table

Finding PrimaryKey columns of a table

From
swalker
Date:
Is there a query I can run against the pg system tables to figure out what
columns of the table make up the primary key?

I've messed around w/ psql -E (which is very cool btw) and can't seem to
find the exact query I'm looking for.  I can get all indexes for a table,
but I can't seem to limit the result set to only the primary key.

Am I missing something obvious ?

Thanks
Steve




Re: Finding PrimaryKey columns of a table

From
rmcm@compsoft.com.au
Date:
The following may help, substitiuting "tablename";

        select a.attname
        from pg_index i, pg_attribute a
        where i.indrelid =
          (select oid as class_oid from pg_class where relname = 'tablename')
        and i.indkey[0] = a.attnum
        and a.attrelid = i.indrelid
        and i.indisprimary = 't';

swalker writes:
 > Is there a query I can run against the pg system tables to figure out what
 > columns of the table make up the primary key?
 >
 > I've messed around w/ psql -E (which is very cool btw) and can't seem to
 > find the exact query I'm looking for.  I can get all indexes for a table,
 > but I can't seem to limit the result set to only the primary key.
 >
 > Am I missing something obvious ?
 >
 > Thanks
 > Steve
 >
 >

--
Rex McMaster                       rex@mcmaster.wattle.id.au
                     http://www.compsoft.com.au/~rmcm/pgp-pk