On Sun, Jan 11, 2004 at 03:30:14PM -0500, Tom Lane wrote:
"Rick Gigger" <rg*****@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
---------------------------(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
================================================== ==========
el***@varlena.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.
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
