Thread
-
SQL-Query 2 get primary key
Marc Grimme <grimme@atix.de> — 1999-02-19T13:22:34Z
Hi, if I create a table like this: CREATE TABLE test ( id decimal(3) primary key, name varchar(32)); how can I ask postgres which is the primary key from table test? I hope to get something like "id is the primary key from table test.". Any ideas Marc -- ----------------------------------------------------------- Marc Grimme - An der Muehle 1 - 85716 Unterschleissheim Fon: +49 89 37 48 81 22 - +49 89 37 48 92 7 - 3/0 - mail2: grimme@informatik.tu-muenchen.de, grimme@atix.de ----------------------------------------------------------- The UNIX Guru's View of Sex: # unzip ; strip ; touch ; finger ; mount ; fsck ; more ; yes ; umount ; sleep
-
Re: [SQL] SQL-Query 2 get primary key
jose' soares <sferac@bo.nettuno.it> — 1999-02-19T16:32:14Z
You may query pg_indexes as in: select * from pg_indexes where tablename = 'test'; tablename|indexname|indexdef ---------+---------+----------------------------------------------------------------------- test |test_pkey|CREATE UNIQUE INDEX "test_pkey" ON "test" USING btree ("id" "int4_ops") (1 row) Marc Grimme ha scritto: > Hi, > > if I create a table like this: > CREATE TABLE test ( > id decimal(3) primary key, > name varchar(32)); > > how can I ask postgres which is the primary key from table test? > I hope to get something like "id is the primary key from table test.". > Any ideas > Marc > -- > ----------------------------------------------------------- > Marc Grimme - An der Muehle 1 - 85716 Unterschleissheim > Fon: +49 89 37 48 81 22 - +49 89 37 48 92 7 - 3/0 - > mail2: grimme@informatik.tu-muenchen.de, grimme@atix.de > ----------------------------------------------------------- > The UNIX Guru's View of Sex: > # unzip ; strip ; touch ; finger ; mount ; fsck ; more ; yes ; umount ; > sleep -- - Jose' - And behold, I tell you these things that ye may learn wisdom; that ye may learn that when ye are in the service of your fellow beings ye are only in the service of your God. - Mosiah 2:17 - -
Re: [SQL] SQL-Query 2 get primary key
Marc Grimme <grimme@atix.de> — 1999-02-19T17:09:53Z
Hi, jose' soares wrote: > > You may query pg_indexes as in: > > select * from pg_indexes where tablename = 'test'; > > tablename|indexname|indexdef > ---------+---------+----------------------------------------------------------------------- > > test |test_pkey|CREATE UNIQUE INDEX "test_pkey" ON "test" USING > btree ("id" "int4_ops") > (1 row) > > > I read about the pg_indexes table and stuff but I am interested in the "columnname" of the primary key in the specified table. Isn´t this feature very important? Cheers, Marc -- ----------------------------------------------------------- Marc Grimme - An der Muehle 1 - 85716 Unterschleissheim Fon: +49 89 37 48 81 22 - +49 89 37 48 92 7 - 3/0 - mail2: grimme@informatik.tu-muenchen.de, grimme@atix.de ----------------------------------------------------------- The UNIX Guru's View of Sex: # unzip ; strip ; touch ; finger ; mount ; fsck ; more ; yes ; umount ; sleep -
Re: [SQL] SQL-Query 2 get primary key
D'Arcy Cain <darcy@druid.net> — 1999-02-20T04:07:22Z
Thus spake Marc Grimme > if I create a table like this: > CREATE TABLE test ( > id decimal(3) primary key, > name varchar(32)); > > how can I ask postgres which is the primary key from table test? SELECT pg_class.relname, pg_attribute.attname FROM pg_class, pg_attribute, pg_index WHERE pg_class.oid = pg_attribute.attrelid AND pg_class.oid = pg_index.indrelid AND pg_index.indkey[0] = pg_attribute.attnum AND pg_index.indisprimary = 't'; That lists all the primary keys in your database. Add a "WHERE pg_class = 'test'" clause to get the specific table. Note that this makes the assumption that only one field can be in the primary key (no complex primary keys) but I don't think there will ever be more than one the way we declare it now. -- D'Arcy J.M. Cain <darcy@{druid|vex}.net> | Democracy is three wolves http://www.druid.net/darcy/ | and a sheep voting on +1 416 424 2871 (DoD#0082) (eNTP) | what's for dinner. -
Re: [HACKERS] Re: [SQL] SQL-Query 2 get primary key
Hannu Krosing <hannu@trust.ee> — 1999-02-20T12:39:44Z
"D'Arcy J.M. Cain" wrote: > > Thus spake Marc Grimme > > if I create a table like this: > > CREATE TABLE test ( > > id decimal(3) primary key, > > name varchar(32)); > > > > how can I ask postgres which is the primary key from table test? > > SELECT pg_class.relname, pg_attribute.attname > FROM pg_class, pg_attribute, pg_index > WHERE pg_class.oid = pg_attribute.attrelid AND > pg_class.oid = pg_index.indrelid AND > pg_index.indkey[0] = pg_attribute.attnum AND > pg_index.indisprimary = 't'; Should it work in 6.4.0 ? It gives an empty table for me ;( > That lists all the primary keys in your database. Add a "WHERE pg_class > = 'test'" clause to get the specific table. You probably mean "pg_class.relname = 'test'" ? > Note that this makes the assumption that only one field can be in the > primary key (no complex primary keys) but I don't think there will > ever be more than one the way we declare it now. Actually you can declare multi_field PK as (Bruce: this probably should be added to \h create table): hannu=> create table test( hannu-> id1 int, hannu-> id2 int, hannu-> meat text, hannu-> primary key (id1,id2) hannu-> ); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index test_pkey for table test CREATE ------------------------- Hannu
-
Re: [HACKERS] Re: [SQL] SQL-Query 2 get primary key
Thomas Lockhart <lockhart@alumni.caltech.edu> — 1999-02-20T16:17:19Z
> > if I create a table like this: > > CREATE TABLE test ( > > id decimal(3) primary key, > > name varchar(32)); <snip> > Note that this makes the assumption that only one field can be in the > primary key (no complex primary keys) but I don't think there will > ever be more than one the way we declare it now. fyi, the following syntax is allowed: CREATE TABLE test ( id decimal(3), name varchar(32), primary key(id)); and multiple columns can be declared as primary keys. - Tom