Обсуждение: locating a primary key

I'm trying to craft a query that will determine what column(s) belong
to the primary key for a given table.  This seems like the kind of
thing that would very common for people to try to do  but my query was
rather ugly and not flexible since it does not work for an arbitrary
number of columns in the primary key.   This is all due to the use of
the int2vect type on indkey which is undocumented and does not seem to
work with normal array tools such as ANY and cant be cast to int2[].

Please tell me there's an easier/better way to do this.

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

Orion <lab@orangekids.org> writes:
> I'm trying to craft a query that will determine what column(s) belong
> to the primary key for a given table.

The information_schema.key_column_usage view might help.

> This is all due to the use of
> the int2vect type on indkey which is undocumented and does not seem to
> work with normal array tools such as ANY and cant be cast to int2[].

As of 8.1 it can ...

            regards, tom lane

Orion wrote:
> I'm trying to craft a query that will determine what column(s) belong
> to the primary key for a given table.  This seems like the kind of
> thing that would very common for people to try to do  but my query was
> rather ugly and not flexible since it does not work for an arbitrary
> number of columns in the primary key.   This is all due to the use of
> the int2vect type on indkey which is undocumented and does not seem to
> work with normal array tools such as ANY and cant be cast to int2[].
>
> Please tell me there's an easier/better way to do this.
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

select * from key_column_usage;
--

            === The PostgreSQL Company: Command Prompt, Inc. ===
      Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
      Providing the most comprehensive  PostgreSQL solutions since 1997
                     http://www.commandprompt.com/