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:
Show quoted text
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 etccreate 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