Re: primary keys

On Wed, 19 Apr 2006 19:39:45 -0700, Orion Henry <lab(at)orangekids(dot)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(at)kgb(dot)une(dot)edu(dot)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 : :
+---------------------------------------+-----------------+