X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9883D52B9D for ; Thu, 18 Aug 2005 13:41:03 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 02480-07 for ; Thu, 18 Aug 2005 16:41:00 +0000 (GMT) Received: from web51906.mail.yahoo.com (web51906.mail.yahoo.com [206.190.48.69]) by svr1.postgresql.org (Postfix) with SMTP id 8555252C18 for ; Thu, 18 Aug 2005 13:40:58 -0300 (ADT) Received: (qmail 38364 invoked by uid 60001); 18 Aug 2005 16:40:57 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=gL1K7zel1SuzLaC4QgZSFDJIUVpADZT1nraxY75tKxi9oCWefV0AXrxCexROKL2YKClarpi+qtsdsv7Im4q7z7zVVDGjIM/l3hRtXMC6euL/1gv4EpsIGvOns3zYqwDw1dyvnqNPvm3fHjsyaXA3rwUJPXd/1YuFlFBV1vTVvao= ; Message-ID: <20050818164057.38362.qmail@web51906.mail.yahoo.com> Received: from [80.84.141.227] by web51906.mail.yahoo.com via HTTP; Thu, 18 Aug 2005 09:40:57 PDT Date: Thu, 18 Aug 2005 09:40:57 -0700 (PDT) From: Roger Tannous Subject: Re: A Table's Primary Key Listing To: pgsql-sql@postgresql.org In-Reply-To: <20050818121844.33a09ad2.darcy@druid.net> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.406 required=5 tests=[AWL=0.032, DNS_FROM_RFC_ABUSE=0.374] X-Spam-Level: X-Archive-Number: 200508/145 X-Sequence-Number: 22373 Thanks for your query :) But it only shows the first of the primary keys of tables having multiple primary keys :) This is apparently because of the pg_index.indkey[0] thing, so how can we manage this query in order to get all of the keys :) Thanks in advance, Roger Tannous. --- "D'Arcy J.M. Cain" wrote: > On Thu, 18 Aug 2005 07:36:22 -0700 (PDT) > Roger Tannous wrote: > > Is there any means to get a list of the Primary Keys (or simply the > > Primary Key if there's only one :) ) for a given table using an SQL > query > > Here is what I do in PyGreSQL: > > SELECT pg_namespace.nspname, pg_class.relname,pg_attribute.attname > FROM pg_class > JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace AND > pg_namespace.nspname NOT LIKE 'pg_%' > JOIN pg_attribute ON pg_attribute.attrelid=pg_class.oid AND > pg_attribute.attisdropped='f' > JOIN pg_index ON pg_index.indrelid=pg_class.oid AND > pg_index.indisprimary='t' AND > pg_index.indkey[0]=pg_attribute.attnum > > -- > 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. > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com