Re: 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             :                 :
+---------------------------------------+-----------------+