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

Fri, 19 Feb 1999 14:22:34 +0100
Marc Grimme <grimme@atix.de>
SQL-Query 2 get primary key
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 -



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 -
Fri, 19 Feb 1999 17:32:14 +0100
"jose' soares" <sferac@bo.nettuno.it>
Re: [SQL] SQL-Query 2 get primary key
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

Fri, 19 Feb 1999 18:09:53 +0100
Marc Grimme <grimme@atix.de>
Re: [SQL] SQL-Query 2 get primary key
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.


Fri, 19 Feb 1999 23:07:22 -0500 (EST)
"D'Arcy" "J.M." Cain <darcy@druid.net>
Re: [SQL] SQL-Query 2 get primary key
"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

Sat, 20 Feb 1999 14:39:44 +0200
Hannu Krosing <hannu@trust.ee>
Re: [HACKERS] Re: [SQL] SQL-Query 2 get primary key
> > 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

Sat, 20 Feb 1999 16:17:19 +0000
"Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Re: [HACKERS] Re: [SQL] SQL-Query 2 get primary key