Received: from druid.net (root@druid.net [198.96.119.226]) by hub.org (8.9.2/8.9.1) with ESMTP id IAA09172 for ; Sat, 20 Feb 1999 08:19:02 -0500 (EST) (envelope-from darcy@druid.net) Received: from localhost (2460 bytes) by druid.net via sendmail with P:stdio/R:bind_hosts/T:inet_zone_bind_smtp (sender: ) (ident using unix) id for ; Sat, 20 Feb 1999 08:18:52 -0500 (EST) (Smail-3.2.0.104 1998-Nov-20 #2 built 1999-Feb-16) Message-Id: Subject: Re: [HACKERS] Re: [SQL] SQL-Query 2 get primary key In-Reply-To: <199902200832.IAA09157@linda.lfix.co.uk> from Oliver Elphick at "Feb 20, 1999 08:32:39 am" To: olly@lfix.co.uk (Oliver Elphick) Date: Sat, 20 Feb 1999 08:18:52 -0500 (EST) From: "D'Arcy" "J.M." Cain Cc: darcy@druid.net, 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 Oliver Elphick > >> how can I ask postgres which is the primary key from table test? > > > >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'; > > > >That lists all the primary keys in your database. Add a "WHERE pg_class > >= 'test'" clause to get the specific table. > > I tried this and got 0 rows. Every value of pg_index.indisprimary is > false, although I have nearly 100 tables created with primary keys. > (This is with 6.4.2) Oops. Full primary key support is in current and will be in the next release. You can set these fields yourself manually to allow this query to work. Here is a macro I use in a scripting language I wrote which does this given a table and a field. It should give you the idea of how to do this. @DEFINE KEY UPDATE pg_index SET indisprimary = 't'\ WHERE pg_index.oid in (SELECT pg_index.oid\ 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_class.relname = '$0' AND \ pg_attribute.attname = '$1'); Unfortunately, that still leaves you with the problem of figuring out which fields are primary the first time. Once you have moved to current, this will be done automatically for you when you declare a field to be the primary key. -- D'Arcy J.M. Cain | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner.