Received: from mail.enterprise.net (root@mail.enterprise.net [194.72.192.18]) by hub.org (8.9.2/8.9.1) with ESMTP id DAA83526 for ; Sat, 20 Feb 1999 03:32:50 -0500 (EST) (envelope-from olly@lfix.co.uk) Received: from linda.lfix.co.uk (root@max10-086.enterprise.net [194.72.199.86]) by mail.enterprise.net (8.8.5/8.8.5) with ESMTP id IAA09304; Sat, 20 Feb 1999 08:32:48 GMT Received: from linda.lfix.co.uk (olly@localhost [127.0.0.1]) by linda.lfix.co.uk (8.9.2/8.9.1/Debian/GNU) with ESMTP id IAA09157; Sat, 20 Feb 1999 08:32:39 GMT Message-Id: <199902200832.IAA09157@linda.lfix.co.uk> X-Mailer: exmh version 2.0.2 2/24/98 (debian) To: "D'Arcy" "J.M." Cain cc: pgsql-sql@postgreSQL.org Subject: Re: [HACKERS] Re: [SQL] SQL-Query 2 get primary key In-Reply-To: Message from "D'Arcy" "J.M." Cain of "Fri, 19 Feb 1999 23:07:22 EST." Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Date: Sat, 20 Feb 1999 08:32:39 +0000 From: "Oliver Elphick" "D'Arcy" "J.M." Cain wrote: >Thus spake Marc Grimme >> if I create a table like this: >> CREATE TABLE test ( >> id decimal(3) primary key, >> name varchar(32)); >> >> 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) -- Oliver Elphick Oliver.Elphick@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver PGP key from public servers; key ID 32B8FAA1 ======================================== "For the Lord himself shall descend from heaven with a shout, with the voice of the archangel, and with the trump of God; and the dead in Christ shall rise first; Then we which are alive and remain shall be caught up together with them in the clouds, to meet the Lord in the air; and so shall we ever be with the Lord." I Thessalonians 4:16,17