Re: how do I get the primary key
От | elein |
---|---|
Тема | Re: how do I get the primary key |
Дата | |
Msg-id | 20040111181929.F12147@cookie.varlena.com обсуждение исходный текст |
Ответ на | Re: how do I get the primary key (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-general |
On Sun, Jan 11, 2004 at 03:30:14PM -0500, Tom Lane wrote: > "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 > > ---------------------------(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@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.
В списке pgsql-general по дате отправления: