Received: from druid.net (root@druid.net [216.126.72.98]) by hub.org (8.9.3/8.9.3) with ESMTP id HAA90472 for ; Mon, 15 Nov 1999 07:34:08 -0500 (EST) (envelope-from darcy@druid.net) Received: from localhost (2752 bytes) by druid.net via sendmail with P:stdio/R:bind_hosts/T:inet_zone_bind_smtp (sender: ) (ident using unix) id for ; Mon, 15 Nov 1999 07:34:07 -0500 (EST) (Smail-3.2.0.104 1998-Nov-20 #4 built 1999-Apr-13) Message-Id: Subject: Re: [SQL] how can tell if a column is a primary key? In-Reply-To: <382F925C.31358F09@summersault.com> from Mark Stosberg at "Nov 14, 1999 11:57:15 pm" To: mark@summersault.com Date: Mon, 15 Nov 1999 07:34:06 -0500 (EST) From: "D'Arcy" "J.M." Cain Cc: pgsql-sql@postgreSQL.org Reply-To: pgsql-sql@postgreSQL.org X-Mailer: ELM [version 2.4ME+ PL50 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Thus spake Mark Stosberg > I'm writing some perl code to access arbitrary Postgres tables. As part > of this, I would like to be to tell if a given column is a primary key. > Given a table and a column name, is there is a select statement I can > run on the systems tables to tell me whether or not it's a primary key? Thanks. This is getting to be a FAQ. SELECT pg_class.relname, pg_attribute.attname FROM pg_class, pg_attribute, pg_index WHERE pg_class.oid = pg_attribute.attrelid AND pg_class.oid = pg_index.indrelid AND pg_index.indkey[0] = pg_attribute.attnum AND pg_index.indisprimary = 't'; will give you a list of tables and the primary key. Just add a further test to the WHERE clause to get one table. Also, check all of the indkey array for complex primary keys. Here's another FAQ but one that no one has ever answered that I know of. How do I generalize the above query so that it returns information on all the elements of complex keys? I don't care if I have to put them together myself, just so that I get the info. The following is two queries I tried but neither on is correct. Anyone see what I am trying to accomplish here and know how to do it properly? -- I thought that leaving off the array index might work but it didn't SELECT pg_class.relname, pg_attribute.attname FROM pg_class, pg_attribute, pg_index WHERE pg_class.oid = pg_attribute.attrelid AND pg_class.oid = pg_index.indrelid AND pg_index.indkey = pg_attribute.attnum AND pg_index.indisprimary = 't'; -- Then I thought that an array was like a set but nope. SELECT pg_class.relname, pg_attribute.attname FROM pg_class, pg_attribute, pg_index WHERE pg_class.oid = pg_attribute.attrelid AND pg_class.oid = pg_index.indrelid AND pg_attribute.attnum IN pg_index.indkey AND pg_index.indisprimary = 't'; Any ideas? -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.