Re: how do I get the primary key

"Rick Gigger" <rgigger(at)leadership-solutions(dot)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