Обсуждение: primary keys

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

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