Re: [SQL] how can tell if a column is a primary key?

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(at){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.