Обсуждение: How to get the name of a table's primary key?

Hi,

I'm sorry if this is a stupid question, but how can I obtain the name
of a primary key of a given table? Suppose I've got a simple table
like this:

create table foo (
  foo_pkey int primary key,
  foo_data varchar(50)
);

In a programme I only know the table name "foo" and want to get the
name of the primary key.
Any help on this?

Cheers,
  Chris

--
Christian von Kietzell
mailto: chris@gammu.ath.cx
Jabber: cuboci@charente.de

You can give the primary key a name when you are creating the table just
like you can give indexes, foreign keys, unique etc

create table foo (
   foo_pkey integer not null,
   foo_data varchar(50) not null,
   CONSTRAINT foo_pk PRIMARY KEY(poo_pkey)
)
;

The primary key for this table is called foo_pk

HTH

Darren Ferguson

On Fri, 15 Mar 2002, Christian von Kietzell wrote:

> Hi,
>
> I'm sorry if this is a stupid question, but how can I obtain the name
> of a primary key of a given table? Suppose I've got a simple table
> like this:
>
> create table foo (
>   foo_pkey int primary key,
>   foo_data varchar(50)
> );
>
> In a programme I only know the table name "foo" and want to get the
> name of the primary key.
> Any help on this?
>
> Cheers,
>   Chris
>
> --
> Christian von Kietzell
> mailto: chris@gammu.ath.cx
> Jabber: cuboci@charente.de
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

Hi,

On Fri, Mar 15, 2002 at 11:47:27AM -0500, Darren Ferguson wrote:
> You can give the primary key a name when you are creating the table just
> like you can give indexes, foreign keys, unique etc
>
> create table foo (
>    foo_pkey integer not null,
>    foo_data varchar(50) not null,
>    CONSTRAINT foo_pk PRIMARY KEY(poo_pkey)
> )
> ;
>
> The primary key for this table is called foo_pk

Well, that isn't quite what I meant. I know I can do that. Maybe my
explanation was a bit misleading.

Suppose, I've got the table shown above. I've only got its name. What
I want is the column name the primary key is created on. How can I do
that? Basically, which of foo_pkey and foo_data is the primary key?

Cheers,
  Chris

--
Christian von Kietzell
mailto: chris@gammu.ath.cx
Jabber: cuboci@charente.de

Christian von Kietzell <chris@gammu.ath.cx> writes:
> Suppose, I've got the table shown above. I've only got its name. What
> I want is the column name the primary key is created on. How can I do
> that? Basically, which of foo_pkey and foo_data is the primary key?

You poke around in the system catalogs.  Look in pg_index for a row that
describes an index on your table (join indrelid to pg_class.oid) and has
indisprimary true.  (If no such row, there's no primary key.)  Then look
in pg_attribute to get the column name(s) based on the column numbers
you see in indkey.  See
http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/catalog-pg-index.html

            regards, tom lane