primary keys

On Wed, 19 Apr 2006 19:39:45 -0700, Orion Henry <lab@orangekids.org> wrote:

I'm trying to craft a query that will determine what column(s) are the
primary key for a given table. I have succeeded but the query is so
ugly that it borders on silly and cannot work for an arbitrary number of
tables since indkey is an int2vect and the ANY keyword does not work on
it.

Please tell me there's an easier way to do this. Here is the query for
tablename $table.

SELECT attname
FROM pg_index
JOIN pg_class ON (indrelid = pg_class.oid)
JOIN pg_attribute ON (attrelid = pg_class.oid)
WHERE indisprimary IS TRUE
AND (attnum = indkey[0] OR attnum = indkey[1] OR attnum = indkey[2])
AND relname = '$table';

Orion

works for me on version 8.1.3

SELECT attname
FROM pg_index
JOIN pg_class ON (indrelid = pg_class.oid)
JOIN pg_attribute ON (attrelid = pg_class.oid)
WHERE indisprimary IS TRUE
AND attnum = any(indkey)
AND relname = $tablename;

or on v7 you could try

select pcl.relname,
(select array_accum(attname) from pg_attribute where attrelid = pco.conrelid and attnum = any(pco.conkey)) as cols

from pg_constraint pco

join pg_class pcl on pcl.oid = pco.conrelid
where pcl.relname = $tablename
and pco.contype = 'p'

klint.

+---------------------------------------+-----------------+
: Klint Gore                            : "Non rhyming    :
: EMail   : kg@kgb.une.edu.au           :  slang - the    :
: Snail   : A.B.R.I.                    :  possibilities  :
: Mail      University of New England   :  are useless"   :
:           Armidale NSW 2351 Australia :     L.J.J.      :
: Fax     : +61 2 6772 5376             :                 :
+---------------------------------------+-----------------+

In article <4447021E23B.9BA8KG@129.180.47.120>,
Klint Gore <kg@kgb.une.edu.au> wrote:

% works for me on version 8.1.3
%
% SELECT attname
% FROM pg_index
% JOIN pg_class ON (indrelid = pg_class.oid)
% JOIN pg_attribute ON (attrelid = pg_class.oid)
% WHERE indisprimary IS TRUE
% AND attnum = any(indkey)
% AND relname = $tablename;

This will work on 7.4, 8.0, or 8.1

SELECT attname
FROM pg_index
JOIN pg_class as c1 ON (indrelid = c1.oid)
JOIN pg_class as c2 ON (indexrelid = c2.oid)
JOIN pg_attribute ON (attrelid = c2.oid)
WHERE indisprimary
AND c1.relname = $tablename
;

No arrays are hurt by this query.
--

Patrick TJ McPhee
North York Canada
ptjm@interlog.com