From: "D'Arcy" "J.M." Cain <darcy@druid.net>
To: mark@summersault.com
Cc: pgsql-sql@postgreSQL.org
Subject: Re: [SQL] how can tell if a column is a primary key?
Date: Mon, 15 Nov 1999 07:34:06 -0500 (EST)
Message-ID: <m11nLKh-0000bFC@druid.net> (raw)
In-Reply-To: <382F925C.31358F09@summersault.com>
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 <darcy@{druid|vex}.net> | 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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: darcy@druid.net, mark@summersault.com
Subject: Re: [SQL] how can tell if a column is a primary key?
In-Reply-To: <m11nLKh-0000bFC@druid.net>
References: <382F925C.31358F09@summersault.com> <m11nLKh-0000bFC@druid.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox