Thread: 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