X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 75A3F9DC801 for ; Tue, 31 Jan 2006 14:28:46 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 18784-02 for ; Tue, 31 Jan 2006 14:28:45 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from oss4u.com (mail.oss4u.com [64.142.76.151]) by postgresql.org (Postfix) with SMTP id 43B739DC89F for ; Tue, 31 Jan 2006 14:28:43 -0400 (AST) Received: (qmail 15327 invoked from network); 31 Jan 2006 18:29:59 -0000 Received: from tiburon.oss4u.com (192.168.1.4) by gw.oss4u.com (192.168.1.1) with ESMTP; 31 Jan 2006 18:29:59 -0000 From: "Uwe C. Schroeder" To: pgsql-admin@postgresql.org Subject: Re: query to select the table primary key column name Date: Tue, 31 Jan 2006 10:28:42 -0800 User-Agent: KMail/1.7.1 Cc: =?iso-8859-1?q?D=FCster_Horst?= , "'pgsql-admin@postgresql.org'" References: <5B025B1F39D6D4119F5700508BEEEC6603DE3FCC@srsofaioi4546.ktso.ch> In-Reply-To: <5B025B1F39D6D4119F5700508BEEEC6603DE3FCC@srsofaioi4546.ktso.ch> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit Content-Disposition: inline Message-Id: <200601311028.42438.uwe@oss4u.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.113 required=5 tests=[AWL=0.113] X-Spam-Score: 0.113 X-Spam-Level: X-Archive-Number: 200601/329 X-Sequence-Number: 20417 Check the information_schema views. Particularly you want to look into information_schema.key_column_usage and information_schema.table_contraints The later gives you the constraint type (in your case 'PRIMARY KEY') and the constraint name which you then can match to the constraint name in key_column_usage to get all the columns that make up the primary key. Hope that helps. BTW: using the information schema is the bettwe way to go for system catalog queries. Every major release will potentially have changes in the system catalogs, where the views in the information schema won't change a lot. They're designed to give you a stable API for the system catalogs. On Tuesday 31 January 2006 08:48, Düster Horst wrote: > I'm looking out for a system catalog query to select the primary key column > name for a specific table. With the following query I only get the name of > the primary key itself. How do I get the primary key column name??? > > select pg_constraint.*,pg_index.indisprimary > from pg_constraint,pg_namespace, pg_class, pg_index > where pg_namespace.nspname='public' > and pg_namespace.oid=c.connamespace > and pg_class.relname='new_layer' > and pg_class.oid=c.conrelid > and pg_class.oid=pg_index.indrelid > and c.contype='p' > > I'll appeciate any help. > > with best regards > > Dr. Horst Düster > > Kanton Solothurn  > Amt für Geoinformation > Abteilung SO!GIS Koordination > Rötistrasse 4 > CH-4501 Solothurn > > Tel.: ++41 (0)32 627 25 32 > Fax: ++41 (0)32 627 22 14 > > horst.duester@bd.so.ch > www.sogis.so.ch > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org -- UC -- Open Source Solutions 4U, LLC 1618 Kelly St Phone: +1 707 568 3056 Santa Rosa, CA 95401 Cell: +1 650 302 2405 United States Fax: +1 707 568 6416