Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id TAA17475 for ; Mon, 10 Apr 2000 19:35:17 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id TAA26645; Mon, 10 Apr 2000 19:35:12 -0400 (EDT) To: "Matlack, Brad" cc: "'pgsql-sql@postgresql.org'" Subject: Re: Finding primary keys In-reply-to: <0918BE615199D311B4F500902785F904973816@kssgqm01.ogden.disa.mil> References: <0918BE615199D311B4F500902785F904973816@kssgqm01.ogden.disa.mil> Comments: In-reply-to "Matlack, Brad" message dated "Mon, 10 Apr 2000 16:37:53 -0600" Date: Mon, 10 Apr 2000 19:35:12 -0400 Message-ID: <26622.955409712@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200004/99 "Matlack, Brad" writes: > I'm trying to determine which fields in a table are primary keys, using a > select statement. This was just discussed in connection with the ODBC driver. The best solution I saw was select ta.attname, ia.attnum from pg_attribute ta, pg_attribute ia, pg_class c, pg_index i where c.relname = 'foo_pkey' AND c.oid = i.indexrelid AND ia.attrelid = i.indexrelid AND ta.attrelid = i.indrelid AND ta.attnum = i.indkey[ia.attnum-1] ORDER BY ia.attnum; where 'foo' is the table you are interested in. You could also do just select attname, attnum from pg_attribute a, pg_class c where c.relname = 'foo_pkey' AND attrelid = c.oid AND attnum > 0 ORDER BY attnum; but this'll not track renaming of columns via ALTER TABLE RENAME COLUMN, since what it's showing you is actually the names of the columns in the index relation, and ALTER doesn't bother to change those. regards, tom lane