Received: from kodu.home.ee (isdn-162.uninet.ee [194.204.19.190]) by hub.org (8.9.2/8.9.1) with ESMTP id HAA05739 for ; Sat, 20 Feb 1999 07:36:30 -0500 (EST) (envelope-from hannu@trust.ee) Received: from trust.ee (hannu@localhost [127.0.0.1]) by kodu.home.ee (8.8.7/8.8.7) with ESMTP id OAA00526; Sat, 20 Feb 1999 14:39:44 +0200 Sender: hannu@kodu.home.ee Message-ID: <36CEAD10.A4582464@trust.ee> Date: Sat, 20 Feb 1999 14:39:44 +0200 From: Hannu Krosing Organization: Trust-O-Matic =?iso-8859-1?Q?O=DC?= X-Mailer: Mozilla 4.5 [en] (X11; I; Linux 2.2.1 i686) X-Accept-Language: en MIME-Version: 1.0 To: "D'Arcy J.M. Cain" CC: pgsql-hackers , Bruce Momjian Subject: Re: [HACKERS] Re: [SQL] SQL-Query 2 get primary key References: Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit "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'; Should it work in 6.4.0 ? It gives an empty table for me ;( > That lists all the primary keys in your database. Add a "WHERE pg_class > = 'test'" clause to get the specific table. You probably mean "pg_class.relname = 'test'" ? > Note that this makes the assumption that only one field can be in the > primary key (no complex primary keys) but I don't think there will > ever be more than one the way we declare it now. Actually you can declare multi_field PK as (Bruce: this probably should be added to \h create table): hannu=> create table test( hannu-> id1 int, hannu-> id2 int, hannu-> meat text, hannu-> primary key (id1,id2) hannu-> ); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index test_pkey for table test CREATE ------------------------- Hannu