Re: how do I get the primary key

On Sun, Jan 11, 2004 at 03:30:14PM -0500, Tom Lane wrote:
> "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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

The information_schema is helpful if you know what you
are looking for. In the standard pg_catalog, these
queries are what I use to see indexes, triggers and
constraints.

From PostgreSQL General Bits Issue#47 (www.varlena.com/GeneralBits/archive.php)

Informational Queries
Here are some queries to show you the constraints, triggers and indexes
for your specific table or tables. To use these queries, substitute the
table name in question for each of the where clauses, or wrap the query into
a function passing in the table name.

-- What indexes are on my table?
select * from pg_indexes where tablename = 'tablename';

-- What triggers are on my table?
select c.relname as "Table", t.tgname as "Trigger Name",
t.tgconstrname as "Constraint Name", t.tgenabled as "Enabled",
t.tgisconstraint as "Is Constraint", cc.relname as "Referenced Table",
p.proname as "Function Name"
from pg_trigger t, pg_class c, pg_class cc, pg_proc p
where t.tgfoid = p.oid and t.tgrelid = c.oid
and t.tgconstrrelid = cc.oid
and c.relname = 'tablename';

-- What constraints are on my table?
select r.relname as "Table", c.conname as "Constraint Name",
contype as "Constraint Type", conkey as "Key Columns",
confkey as "Foreign Columns", consrc as "Source"
from pg_class r, pg_constraint c
where r.oid = c.conrelid
and relname = 'tablename';

elein
============================================================
elein(at)varlena(dot)com Varlena, LLC www.varlena.com

PostgreSQL Consulting, Support & Training

PostgreSQL General Bits http://www.varlena.com/GeneralBits/
=============================================================
I have always depended on the [QA] of strangers.