Re: how do I get the primary key

"Rick Gigger" <rgigger@leadership-solutions.net> writes:
> I need to know how to find out programattically what fields are in the
> primary key of a given table.  Is this possible in postgres?

As of 7.4 the best way is to use the information_schema views.
For example,

regression=# create table fooey (f1 int, f2 int, primary key(f1,f2));
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "fooey_pkey" for table "fooey"
CREATE TABLE
regression=# select * from information_schema.constraint_column_usage where table_name = 'fooey' and constraint_name =
'fooey_pkey';
 table_catalog | table_schema | table_name | column_name | constraint_catalog | constraint_schema | constraint_name
---------------+--------------+------------+-------------+--------------------+-------------------+-----------------
 regression    | public       | fooey      | f1          | regression         | public            | fooey_pkey
 regression    | public       | fooey      | f2          | regression         | public            | fooey_pkey
(2 rows)

(For best results you'd want to constrain table_schema as well as
table_name, but I was lazy...)

In prior versions you can pull out the information by looking at the
underlying system catalogs --- pg_index is the place to start.  See the
developer documentation of the system catalogs.

            regards, tom lane