Thread: how can tell if a column is a primary key?
Hello! 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. -mark -- <<-------------------------------------------------------------->> personal website < Summersault Website Design http://mark.stosberg.com/ > http://www.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.attrelidAND 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.attrelidAND 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.attrelidAND 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.
"D'Arcy" "J.M." Cain <darcy@druid.net> writes:
> 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?
indkey and indclass are type int28 and oid8 respectively, which are
almost like 8-element arrays, except that for historical reasons they
index from 0 not 1. So instead of pg_index.indkey[0] = pg_attribute.attnum
write pg_index.indkey[1] = pg_attribute.attnum to find attributes that
are secondary index columns, [2] for tertiary, etc. Unused positions
in indkey[] are filled with zeroes.
If you have code on the client side it'd probably make most sense just
to pull back the whole value of indkey[] and then issue separate SELECTs
to find out what the referenced attributes are, instead of probing 8
times to see if you get anything...
BTW, if indproc is nonzero then you are looking at a functional index,
and in that case the indkey[] array describes the arguments to the
function; there is only one index column, namely the function result.
regards, tom lane
On Sun, 14 Nov 1999, Mark Stosberg wrote: > > Hello! > > 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. youd also need to check all the indices for the table; although the field may not be the pk, it may be in a unique index or part of a unique index. check psql's source code, which has the appropriate select statements. --- Howie <caffeine@toodarkpark.org> URL: http://www.toodarkpark.org "Tell a man that there are 400 billion stars and he'll believe you. Tell him a bench has wet paint and he has to touch it."
Thus spake Tom Lane
> indkey and indclass are type int28 and oid8 respectively, which are
> almost like 8-element arrays, except that for historical reasons they
> index from 0 not 1. So instead of pg_index.indkey[0] = pg_attribute.attnum
> write pg_index.indkey[1] = pg_attribute.attnum to find attributes that
> are secondary index columns, [2] for tertiary, etc. Unused positions
> in indkey[] are filled with zeroes.
I understand all that. I was just looking for a SQL statement to pull
them all out. If we can't manipulate arrays in SQL I'm not sure that
they really belong here, especially in system tables. Perhaps indkey
should be pulled out to another table instead.
> If you have code on the client side it'd probably make most sense just
> to pull back the whole value of indkey[] and then issue separate SELECTs
> to find out what the referenced attributes are, instead of probing 8
> times to see if you get anything...
Seems real klugy. The system should be able to handle it in the backend.
--
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.